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 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'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.

3 Replies

  • 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. 

       

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.