Actuals Base for Future projection

[VMoza]

Hi!

Would require help on figuring out a DAX measure on how to properly calculate future projection of the stock based on multiple current dataipoints. The starting should be an Actual figure, after which it should take the calculated amount and have it as a base for next month.

Example:

I have actual data for Stock, Sales and Production until current month, from next month I would need to create a projection where the stock = PrevMonth Stock + PrevMonth Production - PrevMonth Sales.

Given that for fx April, I have all actuals, I would need to base it on those, for future months I would need to have forecast data.

Already have measures to SUM actuals until last month and switching to forecast from current month and onwards.

(Already have DIM Date table to connect all of these)

	Stock	Sales	Production
Jan (Actuals)	200	100	150
Feb (Actuals)	250	100	50
March (Actuals)	200	75	75
April (Forecast)	March Actuals (Stock + Production - Sales)	100	75
May (Forecast)	April FC (DAX Calculated April Stock + Production - Sales)	75	100
June (Forecast)	May FC (DAX Calculated May Stock + Production - Sales)	150	100

Hopefully this all makes sense.

Thank you!

[v-yiruan-msft]

Hi @VMoza ,

Please review the following threads which has the similar requirement with yours, hope they can help you resolve the problem.

Iterative Stock Cover Calculation

Inventory calculation using DAX

Best Regards