Calculating Previous Month

[JemmaD]

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.

[v-nuoc-msft · Accepted solution]

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.

[zenisekd]

The reason why it won't work is because you are referencing to month zero from year 2024.  (CurrentYear=2024, CurrentMonth=1, CurrentMonth-1=0).
Better to use PREVIOUSMONTH function? https://learn.microsoft.com/cs-cz/dax/previousmonth-function-dax

Kudos and mark as solution appreciated.

[JemmaD]

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]

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

[JemmaD]

@zenisekd Yes that works, but only if I have a year filtered in the report. 

I want it to work by knowing what the current year is, and work out previous month by itself.

This is what I have but it's not working:

Previous Month = 
CALCULATE (
  [Meetings Count],
  PREVIOUSMONTH('Dates'[Date] ) && YEAR(TODAY() ) ) )

[zenisekd]

In that case I would go with @Daniel29195  solution. In case of previous month you need to specify the year and month...

[Daniel29195]

try this : 

previous month = 

var selectedyear = max ( date[year])

var selectedmonth   = max(date[month])

var prev_year = switch( true(), selectedmonth  = 1 , selectedyear - 1 , selectedyear )

var prev_month =  switch ( true(), selectedmonth = 1 , 12 ,  selectedmonth -1  ) 

var result = 

calculate ( [meetings count]  , 

dimdate[year]= prev_year,

dimdatep[month] = prev_month,

all(dimdate)
)

return result

this should work. 
@JemmaD