topic How to get the previous month's value in DAX Commands and Tips

How to get the previous month's value

carlosroston — Wed, 01 Jan 2025 22:23:54 GMT

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

)

Re: How to get the previous month's value

lbendlin — Wed, 01 Jan 2025 23:13:19 GMT

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.

Re: How to get the previous month's value

Anonymous — Thu, 02 Jan 2025 03:26:12 GMT

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.