Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@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 , 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))
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.
User | Count |
---|---|
106 | |
86 | |
81 | |
73 | |
71 |
User | Count |
---|---|
111 | |
102 | |
97 | |
74 | |
67 |