topic Re: DATESYTD Issue (Rolling Cumulation) in DAX Commands and Tips

DATESYTD Issue (Rolling Cumulation)

MischaT — Tue, 17 Jan 2023 08:36:39 GMT

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!

Re: DATESYTD Issue (Rolling Cumulation)

ValtteriN — Tue, 17 Jan 2023 08:46:44 GMT

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.

Re: DATESYTD Issue (Rolling Cumulation)

MischaT — Tue, 17 Jan 2023 09:56:34 GMT

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

and

Shop Sales = IF(

[Shops calc]=BLANK(),

CALCULATE([Shops calc]))

Re: DATESYTD Issue (Rolling Cumulation)

ValtteriN — Wed, 18 Jan 2023 06:58:16 GMT

Hi,

You can simplyfy the DAX a bit:

IF(

isblank([Shops calc]),

[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/

Re: DATESYTD Issue (Rolling Cumulation)

MischaT — Tue, 24 Jan 2023 12:46:35 GMT

Thank you!