Filter measure by previous month

[Kurt4597]

Hello everyone

I have been stuck on this issue for a few hours and really can't seem to fix it. Any help would be greatly appreciated. 

DateDim Table

Deals Table

I have a measure called "Average Cost" which is the Total Cost / Total number of deals. This measure works as expected and can be filtered by whichever month has been selected. My issue is when I try to create a measure for Average cost Last month.  To do this I have created a measure called PreviousDate

PreviousDate measure - This value is generated depending on whatever is selected in the filter. For example I have currently selected May 2022, so the measure shows 2022-04 

I am then trying to filter the Deals table, where the YearMonth Column = Previous Month measure. See below 

What I have tried 

Average Cost Last Month =

CALCULATE([Average Cost],

all('DateDim'[YearMonth]), filter(DateDim, DateDim[YearMonth] = [PreviousDate])

)

I have tried using this formula but it just gives me a blank value.  I have played around with it and tried a few different versions of this formula but I still can not get the Average Cost measure for the previous month to the selected month from date filter. Any help would be massively appreciated!

Sample File

https://easyupload.io/3jpy04

[amitchandak]

@Kurt4597 , Assume you date table is at level of year month

Create Rank on Year month , a new column

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

You can get measures

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

Decode Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[Kurt4597]

Thank you for the help, but the issue is still present. The measures you have created also give BLANK results

[amitchandak · Accepted solution]

@Kurt4597 , Find the attached file after signature