Forum Discussion

Jmenas's avatar
Jmenas
Icon for Advocate III rankAdvocate III
9 years ago
Solved

Day to day difference in cumulative values DAX or Power Query

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. 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

    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

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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. :smileyhappy:

    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

    • sirros_iot's avatar
      sirros_iot
      Icon for Helper III rankHelper III
      8 years ago
      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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.