Need to calculate projected score for future months

[sunil_Bengaluru]

Hi All,

I have  a table with data from Jan to September. Based on which I have to calculate the projections for October to December.

I have attempted to create a calculated column but its not working. Request your help.

Below is the formula I have used:

Sales_Col = IF('Table'[Sales] <> 0, 'Table'[Sales], CALCULATE(AVERAGEX('Table','Table'[Sales] ),DATESINPERIOD('Table'[Month], Max('Table'[Month]),-6,MONTH)))

Thanks

Sunil

[Ashish_Mathur]

Hi,

Why are you writing this as a calculated column?  Why not a measure?  Also, for November 2023, would the forecast be the average of the last 6 known historical months? i.e. April - September 2023. 

[sunil_Bengaluru]

Hi, sorry for the delayed response.
Tried creating a Measure but didnt get correct function.
Also for November, it should be average of May to Oct + 10% of this average

Please advise.

Thanks

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result clearly.

[sunilbsrv2k]

Hi Ashish

Due to restricitions, not able to paste original data.

Please find the data below (dummy data):

The last column is showing the Formula used for October and November

MonthSalesColumn1

Jan-23	66
Feb-23	67
Mar-23	68
Apr-23	69
May-23	70
Jun-23	71
Jul-23	72
Aug-23	73
Sep-23	74
Oct-23	78.65	AVERAGE(K41:K46)+ 0.1*AVERAGE(K41:K46)
Nov-23	80.41917	AVERAGE(K41:K46)+ 0.1*AVERAGE(K41:K46)
Dec-23
Jan-24
Feb-24

[Ashish_Mathur]

Hi,

The second average should be AVERAGE(K42:K47)+ 0.1*AVERAGE(K42:K47)

I don't think i can solve this using DAX because there is a circularity in the calculation here.  The figure of November depends upon the figure of October (which in turn is an average calculated figure).

[lbendlin]

which forecasting methodology are you planning to use?

[sunil_Bengaluru]

Hi, sorry for the delayed response.
I have been assigned to forecast as average of previous 6 months and add 10% to it.