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

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.