Previous Month of Selected Month not Working

Anonymous — Mon, 15 Jul 2024 15:40:40 GMT

Hi, I am calculating some values with the following DAX Formula:

Trial_Actual =
VAR SelectedMonth =
    IF(
        HASONEVALUE('Calendar'[Month Number]),
            SELECTEDVALUE('Calendar'[Month Number]),
            [Latest Month]
    )
VAR var_volume = CALCULATE(SUMX(VW_WATERFALL, VW_WATERFALL[DATA]), VW_WATERFALL[CATEGORY] = "VOLUME")
RETURN
    SUMX(
        FILTER(
            VW_WATERFALL,
            VW_WATERFALL[MES] = SelectedMonth
        ),
    VW_WATERFALL[DATA]/var_volume
)

And it successfuly returns the correct values for the selected month:

But I also need to calculate the values for the previous month and this is what mi current DAX looks like but for some reason it ain't working.

trial_previous =
    VAR P_Month = PREVIOUSMONTH('Calendar'[Date])
    VAR var_vol = CALCULATE(SUMX(VW_WATERFALL, VW_WATERFALL[DATA]), VW_WATERFALL[CATEGORY] = "VOLUME")
RETURN
    CALCULATE(
        SUMX(VW_WATERFALL,
            VW_WATERFALL[DATA])/var_vol,
            PREVIOUSMONTH('Calendar'[Date])
        )

This DAX is returning the values from the previous month BUT are being divided by the volume of current month. That's the problem I haven't figured out a way to fix. I've tried other alternatives of fx PREVIOUSMONTH() like "selected month - 1", but for some reason none have worked out. And in my var_vol variable, if I modify the filter in CALCULATE to "VW_WATERFALL[CATEGORY] = "VOLUME" && P_Month" I get an error that says "A table of multiple values was suppleid where a single value was expected".

So in a nutshell, for my previous-month values DAX formula, I need for my var_vol variable to be the volume from previous month.

Thank you.

Re: Previous Month of Selected Month not Working

GuillaumePower — Mon, 15 Jul 2024 16:59:50 GMT

Hello, if you have to use a stable filter and another filter on changing on the month, I think you should use something like that, whith the intelligence function DateAdd : ,DATEADD(DateTableWithCustomFiscalYear[Date],-1,Month))

You are shifting the month a month before, (you can use too whith Year,Quarter...)

Re: Previous Month of Selected Month not Working

Anonymous — Mon, 15 Jul 2024 17:30:05 GMT

Thank you so much Guillaume! Using DATEADD definitely simplified the approach. Just as I had to create one measure to sum values for "Actual" and one for "Previous", I also created 2 measures to sum the value of "Volume", one for actual and another ofr previous and divided each correspondant measure.

topic Re: Previous Month of Selected Month not Working in DAX Commands and Tips

Previous Month of Selected Month not Working

Re: Previous Month of Selected Month not Working

Re: Previous Month of Selected Month not Working