Forum Discussion

JemmaD's avatar
JemmaD
Helper V
2 years ago
Solved

Calculating Previous Month

Hi there,

I have a DAX formula which isn't working to calculate the previous month's meetings. 

It's just blank, so part of the formula isn't working and i'm hoping for some syntax help as i've tried a few things and nothing's working.

Here is what I have:

 

 

Previous Month = 
VAR CurrentYear = YEAR(TODAY())
VAR CurrentMonth = MONTH(TODAY())
  
  RETURN
    CALCULATE (
        [Meetings Count],
             YEAR('Dates'[Date]) = CurrentYear &&
             MONTH('Dates'[Date]) = CurrentMonth-1 )

 

I know what the issue is - the current year is 2024 and the current month is January so it's looking for a previous month in 2024 and there isn't one. But if I take the CurrentYear variable out, it still doesn't give me a result. I need it to know to show me December 2023 meetings count.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi JemmaD 

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Table"

     

    Create a measure.

     

     

    meeting = 
var last_month =  EOMONTH(TODAY(),-1)

RETURN 
    CALCULATE(
        SUM('Table'[Meetings Count]), 
        FILTER(
            ALL('Table'), 
            MONTH('Table'[date]) = MONTH(last_month)
            &&
            YEAR('Table'[date]) = YEAR(last_month)
        )
    )

     

     

    Here is the result

     

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

7 Replies

    • JemmaD's avatar
      JemmaD
      Helper V
      2 years ago

      I tried using PreviousMonth function and that wasn't working either! Could you help me with some syntax? My DAX isn't the best!

      I managed to get PREVIOUSYEAR working when I have a year filtered in my report, but I want it to know what the year is without a filter.

      • zenisekd's avatar
        zenisekd
        Super User
        2 years ago

        Meetings of last month = CALCULATE ( [Meetings Count], PREVIOUSMONTH('Dates'[Date]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JemmaD 

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Table"

     

    Create a measure.

     

     

    meeting = 
var last_month =  EOMONTH(TODAY(),-1)

RETURN 
    CALCULATE(
        SUM('Table'[Meetings Count]), 
        FILTER(
            ALL('Table'), 
            MONTH('Table'[date]) = MONTH(last_month)
            &&
            YEAR('Table'[date]) = YEAR(last_month)
        )
    )

     

     

    Here is the result

     

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.