I have a design issue and I wondered if someone could help me here.
To simplify my structure, I have a fact table containing:
Once this table loaded, I need to calculate the Forecasted Stock for next weeks. the formula is simple:
[Forecasted Stock W3] = [Actual Stock W2] + [Forecasted Production W2] - [Forecasted Sales W2]
And I don't know how I can do this. How can I access the data from last week during the script ?
I tried to use a tmp Fact table and to the load it into the final one with a field "NextStock" and the formula:
[Actual Stock] + [Forecasted Production] - [Forecasted Sales]
But as lines are not the same, the forecast line does not find the Actual Stock value.
I thought about creating a Mapping table to get the stock, but I have millions of lines and a lot of other dimensions.
Does someone have an idea on this ?
maybe like this
I assume that you need the sum of the forecasted production and sales per week, right?
You could sum this information with loading the resident fact table grouped by week, where Measure_Type = Forecast.
Then you might load your fact table again and lookup() the forecasted values for the given week.
Does this makes sense?