Forum Discussion

trying's avatar
trying
Frequent Visitor
4 years ago
Solved

Dax YTD Calculation over multiple years

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

DAX Commands
Help me
Help Request
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      trying
      Frequent Visitor
      4 years ago

      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?

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.