cancel
Showing results for
Did you mean:

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

1 ACCEPTED SOLUTION
Microsoft

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

2 REPLIES 2
Microsoft

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

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

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.