Forum Discussion

Kurt4597's avatar
Kurt4597
Helper I
3 years ago

Filtering Measure By Date

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

 




 

 

Need Help

5 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    3 years ago

    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's avatar
      Kurt4597
      Helper I
      3 years ago

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

      • rsbin's avatar
        rsbin
        Community Champion
        3 years ago

        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.