Daily Difference

[SimpleChemist]

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!

[DataNinja777]

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,

[SimpleChemist · Accepted solution]

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