Calculate Forecast with Cumulative Totals and Circular Dependency

[cmgovind]

Hi Experts,

I am trying to forecast with very few data points. So I need to go the Cumulative Totals way, the way we do in excel.

Created a sample for you below:

Month	Actual Sales	Cumulative Sales	Days in Month	Cumulative Days in Month	Forecast Sales	Explanation	Result Column
Jan 2019	120	120	31	31	120		120
Feb 2019	97	217	28	59	217		217
Mar 2019	135	352	31	90	352		352
Apr 2019	82	434	30	120	434		434
May 2019		434	31	151	(434/120)*151	IF Col 'B' Isblank then Max(Cumulative Sales)/Cumulative Days of Last Non Blank Actual Total Month * Cumulative Days of Current Blank Month	546.1
Jun 2019		434	30	181	(434/120)*181		654.6

[v-xuding-msft · Accepted solution]

Hi @cmgovind ,

I created a calculated column to implement it that you can reference.

Forecast Sales = 
var max_days = CALCULATE(MAX('Table'[Cumulative Days in Month]),FILTER('Table','Table'[Actual Sales]<>BLANK()))
return
IF('Table'[Actual Sales] = BLANK(),DIVIDE(MAX('Table'[Cumulative Sales]),max_days) * 'Table'[Cumulative Days in Month],'Table'[Cumulative Sales])

I attached my sample that you can download.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cmgovind]

This works great. Thanks for your help 🙂