Forum Discussion

debitac74's avatar
debitac74
New Member
1 month ago
Solved

DAX help with Last Month in CALCULATE Function

Hi

 

I'm trying to get the sum of last months data for a specific data type, I was originally using a hard coded month number but want to move away from that and do it dynamically but am getting a PLACEHOLDER error..

 

Original DAX that worked:

Backlog (last month) =
CALCULATE(
    SUM('KPI Data'[kpiData]),
        'KPI Data'[kpiID] = 4,
        'KPI Data'[month] = 7
    )
 
Updated DAX that gets the error:
Backlog (last month) =
CALCULATE(
    SUM('KPI Data'[kpiData]),
        'KPI Data'[kpiID] = 4,
        'KPI Data'[month] = [Last Month Number]
    )
 
I'm getting the last month number like this:
Last Month Number =
VAR CurrentMaxDate = MAX('Calender'[Date])
VAR LastMonthDate = EDATE(CurrentMaxDate, -1)
RETURN
MONTH(LastMonthDate)
 
Any help would be very appreciated.
 
Thanks
  • MFelix's avatar
    MFelix
    1 month ago

    Hi debitac74 ,

     

     

    This occurs when you try to use an measure as a table filter expression since this is not allow you get this issue.

     

    In this case you must first calculate the month and then use it on your filter expression:

    Backlog (last month) =
Var _LastMonthNumber = [Last Month Number]
Return
CALCULATE(
    SUM('KPI Data'[kpiData]),
        'KPI Data'[kpiID] = 4,
        'KPI Data'[month] = _LastMonthNumber
    )

     

    Check this detailed blog post about this type of errors and how to fix them.

    https://www.sqlbi.com/articles/solving-errors-in-calculate-filter-arguments/

     

     

1 Reply

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    1 month ago

    Hi debitac74 ,

     

     

    This occurs when you try to use an measure as a table filter expression since this is not allow you get this issue.

     

    In this case you must first calculate the month and then use it on your filter expression:

    Backlog (last month) =
Var _LastMonthNumber = [Last Month Number]
Return
CALCULATE(
    SUM('KPI Data'[kpiData]),
        'KPI Data'[kpiID] = 4,
        'KPI Data'[month] = _LastMonthNumber
    )

     

    Check this detailed blog post about this type of errors and how to fix them.

    https://www.sqlbi.com/articles/solving-errors-in-calculate-filter-arguments/