Filtering Measure By Date

[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 

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

-

[rsbin]

@Kurt4597 ,

Looks to me like you are comparing a MonthYear column to a YearMonth value (2022-04).

Try changing your [MonthYear] in your Measure to [YearMonth].

Regards,

[Kurt4597]

Apologies, that was just a mistake I made when pasting over from the sample file. The issue is still present

[rsbin]

@Kurt4597 ,

Can you please post the Measure you have for "Previous Date"?

Unfortunately, my network here won't allow me to access your link above.

[Kurt4597]

PreviousDate = date(if( Values(DateDim[Month]) = 1, Values(DateDim[Year]) -1, Values(DateDim[Year])), if(Values(DateDim[Month]) = 1,12, (Values(DateDim[Month])-1)), "01")

[rsbin]

@Kurt4597 ,

I was able to download your file.  Took a quick look but am sorry that I haven't been able to decipher the issue.  I would like to suggest you re-post your question in a new thread.  This will give someone else an opportunity to try to resolve your issue.  Also fix the typo/error in your Average Cost formula. 

I will give your file another look later today if I get the chance.

Regards,