Record Count when Date is the 'Global' Latest Month minus 1

[Topjacket]

Hi

I have only recently realsied that this is a problem but am now struggling to find a solution. I have a measure that counts records in the latest month minus 1. This appeared to be working fine with the DAX below but I have relaised that if you put this into a table broken down by person (for example) it will give you the latest month minus 1 for each individual. I need it to be the latest month minus 1 for the entire data set but whatever I add seems to break the measure.

LastMonthCases = CALCULATE(SUM(MASTER_DATA[RecordCount]),MASTER_DATA[CreatedMonthAndYear]=edate(max(MASTER_DATA[CreatedMonthAndYear]),-1))

I have tried adding a "Calculate" and "ALL" to the MAX part but it doesn't seem to like that.

Any hints or tips would be great.

Thanks in advance.

[Topjacket]

Thanks amitchandak

Really appreciate the quick reply.

[amitchandak · Accepted solution]

@Topjacket , You need have table with Month year(Say Date)  and Rank, join back with you table on year month

Then you can have column in that table

Month Rank = RANKX(all('Date'),'Date'[year month ],,ASC,Dense)

and have measures like

This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

or

latest -1

Last Month =

var _max = maxx(allselected('Date') ,'Date'[Month Rank])

return

CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=_max -1))