topic Record Count when Date is the 'Global' Latest Month minus 1 in DAX Commands and Tips

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

Topjacket — Mon, 22 Aug 2022 11:40:21 GMT

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.

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

amitchandak — Mon, 22 Aug 2022 12:59:08 GMT

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))

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))

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

Topjacket — Mon, 22 Aug 2022 13:18:16 GMT

Thanks amitchandak

Really appreciate the quick reply.