Forum Discussion

Apple08's avatar
Apple08
Icon for Helper IV rankHelper IV
3 years ago
Solved

Rolling 3 months

Hi All

 

I like to add a measure to filter data and graph for rolling 3 months rather than showing the whole year data.  For example, now is Feb 23, I like the data to show the data in Feb 23, Mar 23 and Apr 23, then next month will be Mar 23, Apr 23, May 23.

 

 I have tried the DAX below however it doesn't work.  Please could anyone help me to fix this DAX:

 

Next 3 Months = IF([Date]>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),"Yes",IF([Date]>=DATE(YEAR(TODAY()&&([Date]<=MONTH(TODAY()+3,DAY(TODAY())),"Yes","No"))

 Many thanks.

Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Apple08 , In case you want 3 month data and month is selected

     

    Rolling 3 = CALCULATE(Sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],Min('Date'[Date ]),3,MONTH))

     

    Last 3 Months Today =
    var _min = eomonth(today(),-1)+1
    var _max = eomonth(today(),3)
    return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Apple08 , In case you want 3 month data and month is selected

     

    Rolling 3 = CALCULATE(Sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],Min('Date'[Date ]),3,MONTH))

     

    Last 3 Months Today =
    var _min = eomonth(today(),-1)+1
    var _max = eomonth(today(),3)
    return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

    • Apple08's avatar
      Apple08
      Icon for Helper IV rankHelper IV
      3 years ago

      Thanks very much amitchandak.  The DAX below works perfectly, just a question should it be named as Rolling 3 Months Today rather than Last 3 Months?

       

      Last 3 Months Today =
      var _min = eomonth(today(),-1)+1
      var _max = eomonth(today(),3)
      return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

       

      However I am unable to have this work: 

      Rolling 3 = CALCULATE(Sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],Min('Date'[Date ]),3,MONTH))

       

      I wonder I might have done something wrong.

      Thanks very much for your help again.