Forum Discussion

grechill's avatar
grechill
Icon for Helper I rankHelper I
6 years ago

Rolling average for year end

Hello,   I'm trying to get a fixed measure to show me a sum of rolling average measure at the end of the year ignoring all other date filters.   I have tried various options like below, but none ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

I did not get it completely. But we have rolling like

Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))

or

YTD = CALCULATE(Average(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

grechill's avatar
grechill
Icon for Helper I rankHelper I
6 years ago

Hi amitchandak ,

 

Those measures would be limited to YTD and would not roll forward, if necessary. They are not usable for forecasting. Im interested in the forecasting in particular. 

I would like that Power BI displays me an END of year forecast no matter what filters Im applying in date slicer. I forecast all upcomming months by rolling avg, then make date slicers invalid with ALL() funcion and then apply year filter. Unforunately, Power BI is producing error, however formula looks logically correct to me. Any ideas where the mistake is?