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!
