Forum Discussion
Solved
Daily Difference
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 ...
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'))
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,
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'))