Dax YTD Calculation over multiple years

[trying]

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

[v-chenwuz-msft · Accepted solution]

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.

[ValtteriN]

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/

[trying]

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?