Forum Discussion

jdugas's avatar
jdugas
Icon for Advocate I rankAdvocate I
9 years ago
Solved

Rolling Average for Future Months

Does anyone have suggestions on how to average the YTD months to a target, and reforecast the remaining months based on that percentage?   For example,   If my first 5 month average sales was $40...
  • Anonymous's avatar
    Anonymous
    9 years ago

    Firstly, here is a method of calculating an X Month average.  I've assumed you have a selection table and made a measure called "YourSelection" that handles how many months you are considering for this average.  I'll explain the selection table next.

    YourMeasure X Mth Avg = CALCULATE( 
    	if(
    		countrows(values('Dim - Date Table'[YearMonth])) = 1, 
    		[YourMeasure], 
    		AVERAGEX(
    			values('Dim - Date Table'[YearMonth]), 
    			[YourMeasure]
    		)
    	),
    	DATESINPERIOD(
    		'Dim - Date Table'[Date], 
    		LASTDATE('Dim - Date Table'[Date]), 
    		[YourSelection], 
    		MONTH
    	)
    )



    Now the selection table could be anything, but the purpose is create a slicer that links to this table and allows the user to pick.  This could be a list of numbers from 1 to 12, or a list of words like "Last Quarter", "Last Month" with corresponding numbers.

    Then this selection is transated using:

     

     

    YourSelection = MIN('Selection Table'[NoofMonths])


    So now you have an X Monthly average.  Do something like

    NextMonthPredict = ([YourMeasure X Mth Avg] / [Targeted]) * [NextMonthTarget]

    Here i assume you have a measure that knows what you targeted each/current month as and that you can calculate next months target.