Forum Discussion
Solved
DATESYTD Issue (Rolling Cumulation)
H there!
I am having an issue with one of my rolling calculations for the below table:
| Number of Apples sold | YTD Apples sold (rolling) | Shop Sales | Apple + Shop Sales | YTD Apple + Shop Sales (Rolling)
(Calculating incorrectly) | Expected outcome | ||
| 2022 | January | 0 | 0 | 0 | 0 | 0 | 0 |
| 2022 | February | 0 | 0 | 0 | 0 | 0 | 0 |
| 2022 | March | 1800 | 1800 | 0 | 1800 | 0 | 1800 |
| 2022 | April | 0 | 1800 | 23000 | 25050 | 23000 | 26850 |
| 2022 | May | 81795 | 83845 | 0 | 83845 | 23000 | 110695 |
| 2022 | June | 0 | 83845 | 95000 | 179095 | 118250 | 289790 |
| 2022 | July | 4750 | 88845 | 0 | 88845 | 118250 | 378635 |
| 2022 | August | 0 | 88845 | 104550 | 193645 | 223050 | 572280 |
| 2022 | September | 0 | 88845 | 26900 | 115995 | 250200 | 688275 |
The current Dax for "YTD Apple + Shop Sales (Rolling)" is
YYTD Apple + Shop Sales (Rolling)= CALCULATE([Apple + Shop Sales],DATESYTD('Table'[Date],"28/02"))
and is not calculating correctly (I have included an expected value)
Please help!
The measure for "YTD Apples sold (rolling)" is similar:
YTD Apples sold (rolling) = CALCULATE(Number of Apples sold],DATESYTD('Table'[Date],"28/02"))
but it works correctly.
Any suggestions?
Thanks as always!
Hi,
You can simplyfy the DAX a bit:
IF(isblank([Shops calc]),0,[Shops calc])
Then create a YTD calculation for this measure and just [YTD Apples sold (rolling)]+[Shop Sales (rolling)]. [Since Apple + Shop Sales]] already has YTD calculation within it it isn't necessary to calculate it again for the [YTD Apples sold (rolling)] part.
In short create separate YTD calculations and + these together.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
4 Replies
- ValtteriN
Community Champion
Hi,
What kind of calculation are you using in your [Apple + Shop Sales]? The issue is with DATESYTD interaction with this measure so that dax should be revised.Hello!
Thank you for your response
Please see below:
Apple + Shop Sales = CALCULATE([YTD Apples sold (rolling)]+[Shop Sales])
And Shop sales are calculated by two measures:1. Shops calc = CALCULATE( SUM(Shop[ShopNum]),Shop[ShopName]="Shop Sales")andShop Sales = IF([Shops calc]=BLANK(),0,CALCULATE([Shops calc]))- ValtteriN
Hi,
You can simplyfy the DAX a bit:
IF(isblank([Shops calc]),0,[Shops calc])
Then create a YTD calculation for this measure and just [YTD Apples sold (rolling)]+[Shop Sales (rolling)]. [Since Apple + Shop Sales]] already has YTD calculation within it it isn't necessary to calculate it again for the [YTD Apples sold (rolling)] part.
In short create separate YTD calculations and + these together.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
