Day to day difference in cumulative values DAX or Power Query

[Jmenas]

Hi All,

I am trying to build a measure o a column that is telling me the difference between today and yesterday. The issue with the data is that is cumulative. That means if I have a column like costs. I have that "day 3 cost" = day 1 cost + day 2 cost + day 3 cost, and so on. What I need is to have the difference between dates so I can have the "raw" cost for that day. I want to do it in power query but I tried some different way and didn't work. So I tried with DAX (not optimal for my database).  

 I tried different formulas with DAX: 

With Sum and DATEADD
Costs previous day = CALCULATE(Sum(Products[Cost]); DATEADD(Products[Date];-1; DAY))

Cost(Daily) = CALCULATE(SUM(Products[Cost]);LASTDATE(Products[Date])) - [Costs previous day]


With Max and YTD 
Costs previous day = CALCULATE(MAX(Products[Cost]); DATEADD(DatesYTD(Products[Date]);-1; DAY))

Cost(YTD) = CALCULATE(Max(Products[Cost]);DatesyTD(Products[Date])) - [MAX Costs previous day]

My Data:

If someone has an idea how to do it in Power Query or to improve it in DAX. 

Thanks,

J. 

[v-ljerr-msft · Accepted solution]

Hi @Jmenas,

According to your description above, I would suggest you use the formula below to create a new calculate column in your table in this scenario. 

Cost(Daily) = 
VAR costPreviousDay =
    CALCULATE (
        SUM ( Products[Cost] ),
        FILTER (
            ALL ( Products ),
            Products[Date]
                = EARLIER ( Products[Date] ) - 1
                && Products[Container Type] = EARLIER ( Products[Container Type] )
        )
    )
RETURN
    Products[Cost] - costPreviousDay

Regards

[Anonymous]

Hi. I have the same issue but my data table is a bit different.

Same concept. I want to subtract today's value @ 8AM with yesterday's value @ 8AM. Preferably in a measure.

Managed to get a value for the TotToday but a 'blank' for TotPreviousDay.

Please help.

[sirros_iot]

Hello v-ljerr-msft

I would like to know that if I want to use the EARLIER function I need to have a date column. And, can I take this scenario with no calculated columns? Thanks for the help with the answer.