Help with a formula for waterfall comparisons

[dforward3]

Hi everyone. 

Below is the formula I am currently using that is not working as I need it for my waterfall visual. I am trying to compare MTD and YTD values but I now know through validation it is using the wrong periods of time. I have a static date table with all dates that are relevant to my data and then a dynamic date table that outlines MTD, YTD and R12 (as you'll see in my RETURN function) that dictate the time period but I'm not sure if I can somehow use this in my actual CALCULATE functions above.

When using 2022 the values come out that the MTD this year is for September and the YTD is January to September, but when using 2021 MTD is December and YTD is January - December. How am I able to limit the date to only go to the maximum month of the most current year? 

I'm only a few months into learning PBI so I've been picking things up online as I go but this one is stumping me. Thanks for the help!

Total Value Selector Actual =

VAR TVMTD =

-CALCULATE(

    [Total Value],

    FinancialsData[Actual/Budget/Forecast] = "Financial Actual",

    DATESMTD(

        CalendarLookup[Date]

    )

)

VAR TVYTD =

-CALCULATE(

    [Total Value],

    FinancialsData[Actual/Budget/Forecast] = "Financial Actual",

    DATESYTD(

        CalendarLookup[Date]

    )

)

RETURN

SWITCH(

    TRUE(),

    SELECTEDVALUE('DatePeriodsWaterfalls'[Type]) = "MTD",TVWMTD,

    SELECTEDVALUE('DatePeriodsWaterfalls'[Type]) = "YTD",TVWYTD

)

[bradlsco]

dforward3, 

I have a couple of quick question to make sure I understand the situation.  It seems that you have a slicer/filter on the page so that you can select the year.  If so, when you select 2021, what data you are wanting to return?  I'm a bit confused because you say 2021, but then say the most current year, which I interpret as 2022.  Do you mean the max year selected in the slicer/filter?

[dforward3]

The slicer that I'm using on the page is actually a dynamic MTD/YTD slicer, not a manual time selection. I will post below the DAX I'm using for that table. This way, whenever I choose MTD it is giving me all dates in September of 2021 and 2022 respectively, which I'm only using for this Waterfall. My firm isn't concerned with particular dates, just the big time slices of MTD, YTD and R12.

With this, I'm just not sure if I can somehow use the dates from this table in my calculation. My logic in my head would read "if "Type" value is equal to MTD, use those dates to calculate the Total Value". I'm just unsure if I can use that here. 

Sorry if I'm making this more confusing, it's definitely tough explaining structure without being able to show value. If there is also a potentially easier way to show a Waterfall with comparable MTD and YTD values, I'm all ears!

DatePeriodsWaterfalls =

UNION(

    ADDCOLUMNS(

        DATESMTD(CalendarLookup[Date]),

        "Type","MTD",

        "Order",1

    ),

    ADDCOLUMNS(

        DATESYTD(CalendarLookup[Date]),

        "Type","YTD",

        "Order",2

    ),

    ADDCOLUMNS(

        DATESINPERIOD(CalendarLookup[Date],MAX(CalendarLookup[Date]),-12,MONTH),

        "Type","R12",

        "Order",3

    ),

    ADDCOLUMNS(

        SAMEPERIODLASTYEAR(

        DATESMTD(CalendarLookup[Date])),

        "Type","MTD",

        "Order",1

    ),

    ADDCOLUMNS(

        SAMEPERIODLASTYEAR(

        DATESYTD(CalendarLookup[Date])),

        "Type","YTD",

        "Order",2

    ),

    ADDCOLUMNS(

        SAMEPERIODLASTYEAR(

            DATESINPERIOD(CalendarLookup[Date],MAX(CalendarLookup[Date]),-12,MONTH)),

            "Type","R12",

            "Order",3

    )

)

[bradlsco]

Ah.  So you are trying to compare MTD for 2022 against the same period for 2021 (or YTD for 2022 against the same period for 2021)?

[dforward3]

Yep exactly! It's just pushing the months ahead to the latest dates in the previous years since we have full data for that year and incomplete (so far) for this year. I just need some sort of qualifier in the formula to not go past the current last month of the current year for the previous year.