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.

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.