DATEADD doesn't work with cumulative sum

Hi, Yonko 

According to your description, you want to calculate the last week's value of [_Hours remaining].Right?

When you use the function of DATADD(), you get the unexpected value.

First, explain the phenomenon you describe:

Your [_Fact hours cumulative] measure is compared using MAX('Clendar'[Date]), which uses the current filter context, but when you use the CALCULATE() function in the [_WoW] measure to create a filter again Context, and use the DATEADD() function to change the filter context, so that the [_Fact hours cumulative] metric value of the filter context is changed and an incorrect value is calculated.

Here are the steps you can follow：

(1)This is my test data:

(2) I created 3 measures to simulate your first three showing the correct measure:

_Fact hours = SUM('Sheet1'[Fact_duration])

_Fact hours cumulative = CALCULATE([_Fact hours] , FILTER( ALLSELECTED('Calendar') ,'Calendar'[Date] <= MAX('Calendar'[Date]) ))

_Hours remaining = SUM('Table2'[hours_column]) - CALCULATE([_Fact hours] , FILTER( ALLSELECTED('Calendar') ,'Calendar'[Date] <= MAX('Calendar'[Date]) ))

(3)We can create a measure “_WoW” , and then we can meet your need , the result is as follows:

_WoW = SUM('Table2'[hours_column]) - CALCULATE([_Fact hours] , FILTER( ALLSELECTED('Calendar') ,'Calendar'[Date] <= MAX('Calendar'[Date])-7 ))

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly