Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors