topic Daily Difference in DAX Commands and Tips

Daily Difference

SimpleChemist — Thu, 08 May 2025 14:29:37 GMT

Good day!

I have a bit of a problem that I feel should be simple but is stumping me:

Date	Item	Cumulative Sales	Daily Sales
2025-05-05	A	30	5
2025-05-05	B	4	1
2025-05-05	C	15	3
2025-05-04	A	25	10
2025-05-04	B	3	1
2025-05-04	C	12	2
2025-05-03	A	15
2025-05-03	B	2
2025-05-03	C	10

I am trying to calculate the Daily Sales column, which looks at difference per day of each item. I have attempted calculate(max(cumulative sales),dateadd(date,-1,day)), as well as iterations with previousday, allexcept, etc. Any guidance would be splendid!

Re: Daily Difference

DataNinja777 — Thu, 08 May 2025 14:55:07 GMT

Hi SimpleChemist ,

You can achieve your required output in the following manner: Use a calendar table related to your fact table and subtract yesterday’s cumulative value from today’s within the same item context.

Daily Sales := VAR TodayVal = MAX ( Sales[Cumulative Sales] ) VAR YesterdayVal = CALCULATE ( MAX ( Sales[Cumulative Sales] ), DATEADD ( 'Date'[Date], -1, DAY ) ) RETURN TodayVal - COALESCE ( YesterdayVal, 0 )

DATEADD shifts the calendar back one day while the existing filter keeps the same item, giving the daily increment.

Best regards,

Re: Daily Difference

SimpleChemist — Thu, 08 May 2025 15:11:57 GMT

This was actually quite close! (Had never used Coalesce before so that was interesting). Actually found a workaround after posting listed below:

DailyDiff =

var _Date = '[DATE]

var _Cav = '[ID]

var _LastDate = calculate(max([Date]),[ID]=_Cav,[Date]<_Date,all('TABLE'))

return

'[SALES] - CALCULATE(max([SALES]),'[ID]=_Cav,'[DATE]=_LastDate,all('TABLE'))