Last 12 Complete Month Rolling Filter

[matsahiro]

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.

[PaulOlding]

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.

[matsahiro]

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.

[Fowmy]

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

[Fowmy · Accepted solution]

@matsahiro 

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

[matsahiro]

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!