How to get the previous month's value

[carlosroston]

Hello, I would like to obtain a measurement that returns the value of the previous month even if the date filter is unchecked.

I would like it to return the value of the month before the current month if the date filter is not selected.

In this first image the filters are selected and the returned value is correct.

However, in this second image with the filters unchecked, it returns the wrong value. I would like it to return the previous month of the current month.

Below is the DAX function I am using:

Mês Ant =

    CALCULATE(

        [Valor Total],

        DATEADD(

            d_Calendario[Data],

            -1,

            MONTH

        )

    )

[lbendlin · Accepted solution]

Your approach will fail because neither slicer is set to single select.  You are allowing your users to select multiple months as well as multiple years, so "previous month"  loses its meaning.

[Anonymous · Accepted solution]

Hi @carlosroston ,

I create two tables as you mentioned.

Then I create two measures and here are their DAX codes.

Measure = 
IF(
    ISFILTERED('DateTable'[Month]),
    CALCULATE(
        SUM('SalesData'[SalesAmount]),
        FILTER(
            'DateTable',
            'DateTable'[Month] = SELECTEDVALUE('DateTable'[Month])
        )
    ),
    CALCULATE(
        SUM('SalesData'[SalesAmount]),
        FILTER(
            'DateTable',
            'DateTable'[Month] = MONTH(TODAY()) - 1
        )
    )
)

Measure 2 = 'SalesData'[Measure] - 100

But as lbendlin said, you have to keep an option in Slicer or you won't be able to accomplish what you want to accomplish.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

Your approach will fail because neither slicer is set to single select.  You are allowing your users to select multiple months as well as multiple years, so "previous month"  loses its meaning.