Previous Month Filtering

[Kurt4597]

Hello All,

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 

I did initially try the formula 

Average Cost Last Month = CALCULATE([Average Cost], DateDim[YearMonth] = [PreviousDate])

But I am Given This error 

After researching this, I believe the work around was to use all function & filter function, so I produced this 

Average Cost Last Month =

CALCULATE([Average Cost],

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

)

But this just returns a blank value. I need the AVERAGE COST MEASURE but only for values that are in the PREVIOUSMONTH to the current month selected

Any help would be massively appreciated!

Sample File

https://easyupload.io/3jpy04

-

[Jihwan_Kim · Accepted solution]

Hi,

Could you please try something like below and please check whether it suits your requirement?

Average Cost Last Month =
VAR _prevdate = [PreviousDate]
RETURN
    CALCULATE (
        [Average Cost],
        FILTER ( ALL ( DateDim ), DateDim[YearMonth] = _prevdate )
    )

[Kurt4597]

Worked great thank you!