Forum Discussion
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 | 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!
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'))
2 Replies
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'))
