DATEADD doesn't work with cumulative sum

[Yonko]

Hello. I've got the next problem - i want to calculate WoW, and because there isn't any function i use DATEADD function to calculate values a week ago. But there is a problem i can't solve - it doesn't work with cumulative values (i guess it's the reason of problem).
There are 4 measures (for convenience i put them together, also don't mind error lines - i just translated names to English, first 3 work totally fine) - the first one to sum working hours, the second one to gain cumulative sum, the third one to substract cumulative sum from a digit (20), and the forth one should show data a week ago. But it doesn't work properly. For convenience i used 0 interval in DATEADD -  the data should be as in the third column, but for some reason it substracts the first column and not the second one as it should be. I'll be very glad if you'll help me to solve this problem (or will give another solution).
Have a nice day anyway.

[v-yueyunzh-msft · Accepted solution]

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

[Yonko]

Thank you kindly, it worked as intended. I guess that the problem was in me, who could't figure out how to apply all filters correctly. 

[Greg_Deckler]

@Yonko Don't use DATEADD, if you are adding days, just use + [number of days]. Not sure I 100% understand but avoid Time Intelligence functions:

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...