topic Re: Rolling YTD for previous year in DAX Commands and Tips

Rolling YTD for previous year

cosmicyes — Wed, 20 Nov 2024 09:52:47 GMT

Dear Power BI friends,

I am trying to write a measure that gives me the YTD value of my revenue relative to the previous year. Today is November 20, 2024, and I have the revenue in the measure "Umsatz". What I want is the revenue for the period from January 1, 2023, to November 20, 2023.

The measure should roll dynamically, meaning it should respond to changes in the calendar slicers.

I have managed to get it working halfway, but this way, the measure does not respond to changes in the calendar:

Umsatz YTD Vorjahr # = CALCULATE ( CALCULATE ( [Umsatz], DATESYTD ( Kalender[Datum] ) ), Kalender[Datum] = ( DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ) ) - 1 )

Thank you for your help.

Re: Rolling YTD for previous year

bhanu_gautam — Wed, 20 Nov 2024 10:49:06 GMT

cosmicyes , Try using dax

Umsatz YTD Vorjahr =
CALCULATE (
[Umsatz],
DATESYTD (
SAMEPERIODLASTYEAR ( Kalender[Datum] ),
"11/20"
)
)

Re: Rolling YTD for previous year

cosmicyes — Wed, 20 Nov 2024 10:58:11 GMT

bhanu_gautam

thank you very much - but 11/20 ist not flexible and rolling.
What I mean is: it should also work if I select June 2024 in calender and should show the result by June 23 2023 and so on...
Is this possible?

Re: Rolling YTD for previous year

saurabhtd — Wed, 20 Nov 2024 10:59:14 GMT

cosmicyes This DAX will give you result

Umsatz YTD Vorjahr = CALCULATE( [Umsatz], SAMEPERIODLASTYEAR( DATESYTD(Kalender[Datum]) ) )

Re: Rolling YTD for previous year

cosmicyes — Wed, 20 Nov 2024 11:05:50 GMT

Dear bhanu_gautam
this looks really good and I hoped it worked.
But it seems the measure delivers the revenue for the *whole* year 2023.
Am I missing something?

Re: Rolling YTD for previous year

saurabhtd — Wed, 20 Nov 2024 11:45:56 GMT

cosmicyes I think this will work

Umsatz YTD Vorjahr = CALCULATE( [Umsatz], DATESBETWEEN( Kalender[Datum], DATE(YEAR(MAX(Kalender[Datum])) - 1, 1, 1), -- Start of the previous year DATE(YEAR(MAX(Kalender[Datum])) - 1, MONTH(MAX(Kalender[Datum])), DAY(MAX(Kalender[Datum]))) -- End date in the prior year ) )

But if above formula is not filtering with changes in calendar.
Try below code

Umsatz YTD Vorjahr = CALCULATE ( [Umsatz], DATESYTD ( DATEADD ( Kalender[Datum], -1, YEAR ), "12/31" ) )

Re: Rolling YTD for previous year

cosmicyes — Wed, 20 Nov 2024 12:13:09 GMT

bhanu_gautam
Thank you very much.
It still does not work 100% but this is something I can work with!