cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yonko
Helper I
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.

 

image.pngimage.png

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
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:

vyueyunzhmsft_0-1663314412457.png

 

vyueyunzhmsft_1-1663314412461.png

 

vyueyunzhmsft_2-1663314412461.png

 

(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]) ))

vyueyunzhmsft_3-1663314412465.png

 

(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 ))

vyueyunzhmsft_4-1663314412468.png

 

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

View solution in original post

3 REPLIES 3
v-yueyunzh-msft
Community Support
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:

vyueyunzhmsft_0-1663314412457.png

 

vyueyunzhmsft_1-1663314412461.png

 

vyueyunzhmsft_2-1663314412461.png

 

(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]) ))

vyueyunzhmsft_3-1663314412465.png

 

(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 ))

vyueyunzhmsft_4-1663314412468.png

 

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

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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