Forum Discussion

dforward3's avatar
dforward3
Frequent Visitor
3 years ago

Help with a formula for waterfall comparisons

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...
Need Help
bradlsco's avatar
bradlsco
Frequent Visitor
3 years ago

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's avatar
dforward3
Frequent Visitor
3 years ago

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's avatar
    bradlsco
    Frequent Visitor
    3 years ago

    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's avatar
      dforward3
      Frequent Visitor
      3 years ago

      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.