topic Dax YTD Calculation over multiple years in DAX Commands and Tips

Dax YTD Calculation over multiple years

trying — Fri, 02 Sep 2022 11:05:45 GMT

Hi everyone,

i have a problem with a dax formula and i hope you can help me.

I want to have a line chart which displays ytd sales for the last 4 years, based on a slicer where i want to select a month up to which the ytd value should be calculated to. Meaning when i select the month 06 for 2021 i want to have in the y axis of the line chart the following values:

for 2021 --> the ytd sales value for 2021 until the month 06

for 2020 -->the ytd sales value for 2020 until the month 06

for 2019 -->the ytd sales value for 2019 until the month 06

for 2018 -->the ytd sales value for 2018 until the month 06

In the x axis i would then drop the year from my calendar do display the evolution of the ytd sales.

Thanks in advance for your help and KR,

Daniel

Re: Dax YTD Calculation over multiple years

ValtteriN — Fri, 02 Sep 2022 13:24:55 GMT

Hi,

Here is one way to do this:
Data:

Relationship:

Dax:

Measure 19 = CALCULATE(SUM('Table (7)'[Value]),DATESYTD('Calendar'[Date]))

Measure 20 = CALCULATE([Measure 19],SAMEPERIODLASTYEAR('Calendar'[Date]))

Measure 21 = CALCULATE([Measure 20],SAMEPERIODLASTYEAR('Calendar'[Date]))

The idea is to user SAMEPERIODLASTYEAR for each additional year you want to see to past.

End result:

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: Dax YTD Calculation over multiple years

trying — Fri, 02 Sep 2022 14:56:30 GMT

Hi,

first off all thanks for the reply and your effort!

Unfortunately this wont solve my issue. I wanted to have one single measure that can do the trick. Is this possible?

Re: Dax YTD Calculation over multiple years

v-chenwuz-msft — Tue, 06 Sep 2022 08:06:53 GMT

Hi trying ,

Is this you want?

If yes, you need create a measure like the following:

Measure = VAR _ytddate = FILTER ( ALLSELECTED ( 'Table' ), [Date].[MonthNo] <= MAX ( 'Month'[index] ) ) VAR _ytddate_c = FILTER ( _ytddate, [Date] <= MAX ( 'Table'[Date] ) && [Date].[Year] = MAX ( 'Table'[Date].[Year] ) ) RETURN IF ( SELECTEDVALUE ( 'Month'[index] ) < MAX ( 'Table'[Date].[MonthNo] ), BLANK (), SUMX ( _ytddate_c, [Value] ) )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.