# Power BI Exchange

View Only

## How to calculate row count difference based on date

• #### 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:

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:

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

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

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)

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

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

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

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

Attachment(s)

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