Max Less than 1

[keckraguilar]

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]))

[Anonymous · Accepted solution]

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.

[Greg_Deckler]

@keckraguilar Try:

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

[keckraguilar]

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.