Forum Discussion

matsahiro's avatar
matsahiro
Icon for Helper II rankHelper II
5 years ago
Solved

Last 12 Complete Month Rolling Filter

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.
  • Fowmy's avatar
    Fowmy
    5 years ago

    matsahiro 

     

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

     

5 Replies

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    5 years ago

    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's avatar
      matsahiro
      Icon for Helper II rankHelper II
      5 years ago

      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's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Fowmy
      Icon for Super User rankSuper User
      5 years ago

      matsahiro 

       

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

       

      • matsahiro's avatar
        matsahiro
        Icon for Helper II rankHelper II
        5 years ago

        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!