topic Max Less than 1 in DAX Commands and Tips

Max Less than 1

keckraguilar — Thu, 05 Sep 2024 16:50:59 GMT

I want to show sales based on the most recent month minus one. The below dax shows the sales for max month (June). But I am looking to build another measure that Sums May. (Max-1) I cant use the actual month number or month name becuase I want the formula to be dynamic for when I load additional months (August, September, etc)

CALCULATE(SUM([Sales]), [Month Number]=MAX([Month Number]))

Re: Max Less than 1

Greg_Deckler — Thu, 05 Sep 2024 16:56:26 GMT

keckraguilar Try:

Measure = VAR __MaxMonth = MAXX( ALL( 'Table' ), 'Table'[Month Number] ) VAR __Result = SUMX( FILTER( ALL( 'Table' ), [Month Number] = __MaxMonth - 1 ), [Sales] ) RETURN __Result

Re: Max Less than 1

keckraguilar — Thu, 05 Sep 2024 18:41:01 GMT

This Works! I forgot that the table actually has multiple years. (see table below) Is it possible to add a dynamic filter for max year as well? Basically I would only want May from year 2021 and not 2020.

Re: Max Less than 1

Anonymous — Mon, 09 Sep 2024 06:46:42 GMT

Hi keckraguilar

You can create a measure with the following DAX:

PreviousMonthSales = VAR CurrentM = MAX('DataTable'[Month Number]) VAR CurrentY = MAX('DataTable'[Year]) VAR PreviousM = IF(CurrentM = 1, 12, CurrentM - 1) VAR PreviousY = IF(CurrentM = 1, CurrentY - 1, CurrentY) RETURN CALCULATE( SUM('DataTable'[Sales]), FILTER( 'DataTable', 'DataTable'[Month Number] = PreviousM && 'DataTable'[Year] = PreviousY ) )

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