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
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.