topic Re: Running total like YTD, but begin and end on sept 1 in DAX Commands and Tips

Running total like YTD, but begin and end on sept 1

Artefact — Thu, 22 Aug 2024 18:29:10 GMT

Hello,

I think the title says it all! So a DAX command with time intelligence which starts not on jan 1st like YTD but another 1st of a month. And which calculates a running total on a daily basis. And starting again after a full year.

Thanks!

Re: Running total like YTD, but begin and end on sept 1

jgeddes — Thu, 22 Aug 2024 20:35:03 GMT

There is a DAX function DATESYTD which accepts an optional year end date parameter that you could likely use in this scenario.
Here is a link to the learn doc. Learn - DATESYTD

Re: Running total like YTD, but begin and end on sept 1

Artefact — Thu, 22 Aug 2024 23:18:53 GMT

Thanks, but I want the end date to be dynamic, with a slicer. And the parameter you mentioned must be static.

Re: Running total like YTD, but begin and end on sept 1

mark_endicott — Fri, 23 Aug 2024 09:46:14 GMT

Artefact - The solution provided by jgeddes will be dynamic, because you will set the year end date to 31/08, which will mean your YTD calculation always begins at 01/09 and runs to current date.

An example of how you will use this is below:

CALCULATE( [Total Sales], DATESYTD('Date'[Date]', "08-31") )

You will need to make sure you use the correct format for your Date Locale.

Re: Running total like YTD, but begin and end on sept 1

Artefact — Fri, 23 Aug 2024 11:10:01 GMT

I want the end date to be dynamic, and not limited to one date like 31/08. I want the customer to choose th end date. Based on a parameter or selection.

Re: Running total like YTD, but begin and end on sept 1

mark_endicott — Fri, 23 Aug 2024 11:14:28 GMT

Artefact - You don't understand.

In DATESYTD( ) think of the end date that you set in the code as setting a static start date. You just give it the day before you want your YTD to start. You are just setting a static end to the previous year.

The end date of your current year will then be dynamic when you use a slicer etc.

Re: Running total like YTD, but begin and end on sept 1

Sahir_Maharaj — Fri, 23 Aug 2024 11:18:49 GMT

Hello Artefact,

Can you please try the following approach:

RunningTotalSep1 = CALCULATE( [Total Sales], FILTER( ALL('Date'), 'Date'[Date] >= DATE(YEAR(TODAY()) - IF(MONTH(TODAY()) < 9, 1, 0), 9, 1) && 'Date'[Date] <= MAX('Date'[Date]) ) )

Hope this helps.