Forum Discussion
calculate rolling over
Hello,
Hope anyone can help?
I need to create a new measure to calculate the rolling leavers for a period of time. I have the number of leavers on a monthly basis but not sure how to create a formula that looks back 12 month and add the total leavers by region?
Thanks,
Silvia
Anonymous ,
Try like
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))
3 Replies
So if you have a separate date table, you could use time intelligence functions lat TOTALYTD for example.
Also see if my Time Intelligence the Hard Way provides a way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008- Also, I think there is a rolling months Quick Measure. Yep: https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499#M124
Anonymous ,
Try like
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))
