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.00 but I targeted $50, I would take 40/50 and multiple that by next months target, say it was $48. The value would come out to 4/5*48 or 38.4. How would I forecast the rest of the months for the year. Would I need to create a calculated table?

 

Any guidance is much appreciated.

 

Thanks

Jeremy

 

  • 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.

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.

     

    • jdugas's avatar
      jdugas
      Icon for Advocate I rankAdvocate I

      Sorry. WAY too much info for me to swallow. I'm no DAX expert.

       

      I can calculate YTD average, but that's about it. How to I show a forecasted line based on last percentage to target?

      • Anonymous's avatar
        Anonymous
        Not applicable

        You will need a measure that will calculate a target for each month (we'll call it [Future Target].  Then you need a measure that is:  Your Avg / [Target] * [Future Target]