Forum Discussion

keckraguilar's avatar
keckraguilar
Frequent Visitor
1 year ago
Solved

Max Less than 1

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 actu...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
Greg_Deckler
Community Champion
1 year ago

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's avatar
    keckraguilar
    Frequent Visitor
    1 year ago

    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.