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!

    ------------------------------
    ANDREW ESPANA
    IT Data Analytics PM
    Charlotte NC
    9802881065
    ------------------------------


  • 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
    Student
    UOA
    +642102244925
    ------------------------------



  • 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
    CountToday:=CALCULATE(COUNTA(Records[Name]),DateToday,ALL(DateOther))
    CountCompare:=CALCULATE(COUNTA(Records[Name]),ALL(DateToday),DateOther)

    DiffCount(Today-Other):=IF(OR(ISBLANK([CountToday]),ISBLANK([CountCompare])),BLANK(),[CountToday]-[CountCompare])

    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
    Lysaker
    92014186
    ------------------------------

    Attachment(s)

    xlsx
    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 ) )
    RETURN
        [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
    Director
    Canada
    ------------------------------



  • 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.

    CALCULATED COLUMN FOR RANK

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

    MEASURE FOR DELTA

    DELTA =
        VAR PRIOR_COUNT =
            VAR CURRENT_RANK = SELECTEDVALUE( Table1[DATE_RANK] )
            RETURN
                CALCULATE(
                    [Record Count] ,
                    FILTER(
                        ALL( Table1 ) ,
                        Table1[DATE_RANK] = CURRENT_RANK - 1
                    )
                )
        RETURN
            [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!

    Thanks!
    William

    #DAX #PowerBI
    ​​​​

    ------------------------------
    William Rodriguez
    Business Analyst
    ------------------------------

    Attachment(s)

    pbix
    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
    ------------------------------