Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  How to calculate row count difference based on date

    Posted Aug 08, 2018 03:36 PM
    Hello Community, I would appreciate anyone's assistance with the following:

    I have a data set based on upload dates such as this:

    Upload Date       Name
    08/01/2018        John Doe
    08/01/2018        Jane Doe
    08/02/2018        John Doe
    08/02/2018        Jane Doe... and so on.

    I would like to create a table or visualization where at a glance I can see the number of records loaded on each date and a delta, or difference, between the two amounts, such as:

    Upload Date    Record Count     Delta
    08/01/2018      1000                    200
    08/02/2018       800

    Currently, I'm able to create a calculated 'countrows' measure, but I need assistance with calculating a measure to find the delta.

    Any assistance would be greatly appreciated!

    IT Data Analytics PM
    Charlotte NC

  • 2.  RE: How to calculate row count difference based on date

    Posted Aug 09, 2018 02:39 AM
    Try Creating a measure using Previousmonth() and subtracting both the measure to find the delta. Hope this helps

    Vikraman Mohan

  • 3.  RE: How to calculate row count difference based on date

    Silver Contributor
    Posted Aug 09, 2018 04:40 AM
      |   view attached
    Hi Andrew,

    I have two solutions for you with examples in the attached file.

    Solution 1: If you compare counts betweens two dates always separated by the same interval. (ex: Always two consecutive days or always one month apart exactly...)

    You can create new columns:

    TodayCount = COUNTAX(FILTER(Records,Records[UploadDate]=EARLIER(Records[UploadDate]),[Name])
    CountYesterday = COUNTAX(FILTER(Records,Records[UploadDate]=EARLIER(Records[UploadDate])-1,[Name])

    So in your table, you'll now have for each line the total count of the current day and the total count of yesterday (if you need a month, you may need to use DATEADD( ) around the EARLIER() part to set it exactly one month away, one year away...)

    You can then finish easily with simple measures.

    Solution 2: More flexible because you can calculate the difference in count between any two dates of your choice.

    Add two date tables: DateToday and DateCompare
    And link your Records table to both of them.

    Create a few measures


    You can now put the two dates column Today and Compare on Rows and columns. The DiffCOunt measure will give the matrix of Count Differences from each date to each other date.

    Hope that helps

    Martin Dizel
    Project Management / BI Consultant


    CountDiff per Date.xlsx   203 KB 1 version

  • 4.  RE: How to calculate row count difference based on date

    Posted Aug 09, 2018 06:11 AM
    Edited by Yannick Haineault Aug 09, 2018 06:13 AM

    Hi Andrew,

    Here's an example of the DAX code:

    Count delta =
    VAR nextDayCount =
        CALCULATE ( COUNTROWS ( 'Sales Invoices' ), DATEADD ( DateDim[Date], 1, DAY ) )
        [Record Count] - nextDayCount

    In this example, table 'Sales Invoices' contains the transactions. Each transaction has a date and that date is liked to a dateDim table. (Relationship in Power BI)
    The calculated measure "Record Count" is the count of records (Record Count = COUNTROWS('Sales Invoices')).

    Yannick Haineault

  • 5.  RE: How to calculate row count difference based on date

    Top Contributor
    Posted Aug 09, 2018 09:30 AM
      |   view attached
    Greetings @ANDREW ESPANA:

    Great question; the following adequately addresses your needs based on the parameters and example data you uploaded.

    I will break down the solution in two parts - attached is the PBIX file for your reference:
    • The Final Result
    • How To Accomplish

    The Final Result
    In your example, you have a table with upload dates per employee. You need to count the records per date as well as detail the delta between the current date and the prior date*. Below is the image result of the completed task:

    Notice how the dates are not always sequential, and you need to be able to account for this (i.e., 9/24 references the last date with values and takes the delta)
    *your example looks like you want the delta between the current date and the future date, which does not make sense to me. Albeit, if these are the requirements, I will show you how to accommodate the DELTA measure below

    How To Accomplish
    The premise boils down to indexing the values of the current dates, and then referencing the last indexed value in conjunction with the current index value for your delta. To accomplish this:
    1. Create an index in your model based on the date**
    2. Create a measure that calculates the current value and the last value to find the delta

    **In the future, if you want to include more attributes in your report, you will have to take these into account for your ranking schema.


    = RANKX( Table1 , Table1[UploadDate] ,, ASC , Dense)
    --END DAX


    DELTA =
                    [Record Count] ,
                        ALL( Table1 ) ,
                        Table1[DATE_RANK] = CURRENT_RANK - 1
            [Record Count] - PRIOR_COUNT
    --END DAX

    *Note from above, if you want the future value rather than the previous value, change the filter from:
    • (PRIOR RANK):
      • Table1[DATE_RANK] = CURRENT_RANK - 1
    • (FUTURE RANK):
      • Table1[DATE_RANK] = CURRENT_RANK + 1

    Once again, hope this adequately address your question. If I can be of further assistance, let me know!


    #DAX #PowerBI

    William Rodriguez
    Business Analyst


    Andrew_Help.pbix   41 KB 1 version

  • 6.  RE: How to calculate row count difference based on date

    Posted Oct 23, 2022 06:28 AM
    How do I drill through the delta to show the records in visualization?

    Ayesha Sultana