YTD for morethan one year in DAX

Question

I have a scenario where I need to calculate the YTD for two years.

For eg : If user selects Nov-2020, Oct-2019 , then I need to return YTD(Nov-2020)+ YTD(Oct-2019).

DatesYTD function returns for the latest year, it doesn't sumup. Is there is someway where I can sum for both the years.

I need to sumup for both the years. They can select differnt years, for eg : Nov 2019 and Feb -2020, — Sruthi Sirekollam, Dec 15 '20 at 09:42
What date/range exactly end user selecting? can you show a snapshot of the date slicer? — mkRabbani, Dec 15 '20 at 09:56
I can see an user can select multiple value from the slicer. What if some one select sep-2020 and Nov-2020. Both belongs to same year. And what if someone select more than 2 month from the slicer? — mkRabbani, Dec 15 '20 at 10:17
requirement is that whatever they select the sum of those YTD's need to be shown . I know its bit weird , but need some solution — Sruthi Sirekollam, Dec 15 '20 at 10:27
So if someone select Oct-2020 and Nov-2020, you need SUM(Jan to Oct 2020) + SUM(Jan to Nov 2020), right? Values from Jan-Oct will be available in both part that case and the total will be incorrect that case. — mkRabbani, Dec 15 '20 at 10:51
I'm checking with them on the same. but in case they select oct 2020 and aug 2019, is there is way where I can do sum(YTD(Aug 2019)) + YTD(oct 2020) — Sruthi Sirekollam, Dec 15 '20 at 11:13
Is there any limit - how many values a person can select? If only 2 value can select, there is a chance for you. If more than 2 select and you have option of taking MIN and MAX from them, also there is a chance for you :) — mkRabbani, Dec 15 '20 at 11:19

score 0 · Answer 1 · answered Jan 01 '21 at 21:45

Assuming that the problem is to add up the year to dates of different year months selection made on the slicer and that in case of multiple selection in the same year, the last month should be used, a possible solution is to iterate over the years, selecting the max date for the currently iterated year and compute the YTD based on this max date. Then add up the resulting YTDs

YTD Total =
SUMX(
    VALUES( 'Date'[Year] ),
    VAR MaxDate =
        CALCULATETABLE( LASTDATE( 'Date'[Date] ) )
    RETURN
        CALCULATE( TOTALYTD( [Amount], 'Date'[Date] ), MaxDate )
)

YTD for morethan one year in DAX

1 Answers1