Forum Discussion

Apple08's avatar
Apple08
Helper 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 ...
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))

amitchandak's avatar
amitchandak
Super 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
Helper 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.