cancel
Showing results for
Did you mean:
Helper I

DATEADD doesn't work with cumulative sum

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.

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

Helper I

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.

Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors