topic Last 12 Complete Month Rolling Filter in DAX Commands and Tips

Last 12 Complete Month Rolling Filter

matsahiro — Wed, 12 May 2021 22:58:27 GMT

Hello, I have a visual where I would like to display only data for the last 12 complete months. For example, if today is 5/12/2021, the data I would like to be displayed would be running from 4/1/2020 to 4/30/2021. Every month has a full month's worth of data.

Right now I have this DAX, but PBI gives me errors on formatting and values when comparing the dates for some reason.

Rolling 12 Month Ind =

VAR Start_Date = DATE(YEAR(TODAY()-1),MONTH(TODAY())-1,1)

/*Returns date 13 months ago and at beginning of month. Made sure to start at beginning of month to make sure month is complete*/

VAR End_Date = EOMonth(TODAY(), -1)

/*Not sure if this is the best route, because if TODAY() is the end of the month already, I would like to include that data.

RETURN IF(Start_Date <= SELECTEDVALUE(Sales[Date_Sold <= End_Date, 1, 0)

Any suggestions on how to tweak this indicator for filtering? Thank you in advance.

Re: Last 12 Complete Month Rolling Filter

Fowmy — Thu, 13 May 2021 00:25:49 GMT

matsahiro

Please use the following formula to have the desired results:

Rolling 12 Month Ind = VAR CurrentDate = SELECTEDVALUE(Sales[Date_Sold]) VAR Start_Date = EOMONTH( TODAY() , -14 )+1 VAR End_Date = EOMONTH ( TODAY(), -1 ) RETURN IF ( CurrentDate >= Start_Date && CurrentDate <= End_Date, 1, 0 )

Re: Last 12 Complete Month Rolling Filter

PaulOlding — Thu, 13 May 2021 06:59:51 GMT

This is the DAX forum so it's no surprise you've got a DAX answer. I'd just point out this functionality is available in the UI. Use a relative date slicer or filter with calendar month.

Re: Last 12 Complete Month Rolling Filter

matsahiro — Thu, 13 May 2021 13:29:14 GMT

Hi Paul. You are correct that it is available in the UI. But if you notice, this does not meet my requirements as the data that is being filtered in does not start at the beginning and end of each month.

Re: Last 12 Complete Month Rolling Filter

Fowmy — Thu, 13 May 2021 17:10:04 GMT

matsahiro

Hope you tried my solution to your problem, let me know if it works.

Re: Last 12 Complete Month Rolling Filter

matsahiro — Thu, 13 May 2021 19:52:17 GMT

Definitely gave it a go. The IF statement ended up not working for a specific visual I had, but I simply threw in the same logic in a CALCULATE statement.

Rolling 12 Month Count =

VAR Start_Date = EOMONTH(TODAY(), -14)+1

VAR End_Date = TODAY()

RETURN

CALCULATE(COUNT(Table[Column]), Table[Column] >= Start_Date && Table[Date] <= End_Date)

Thank you very much for the help!