Hi,
Here's the sample data with calculations done in excel (https://www.dropbox.com/s/4zfdyua1wv75ar6/Sample%20Data.xlsx?dl=0). I'm not exactly sure how to do the same calculations in Power BI.
Basically for each stock_month/country/material/batch combination, I need to determine if it's in excess or not. I have a 12-month range forecast data coming in for each stock_month/country/material combination.
A stock unit is deemed as excess when it cannot be used before it's expiry date and the quantity available is more than the forecast quantity.
The way I did it in excel:
1) I sorted the data table by stock_month, material, and expiry date
2) For each stock_month/country/material/batch combination, I summed all the forecast quantity (from forecast data table) with 'month' date field before the expiry date listed for that line
3a) Effective Forecast for the first stock_month/country/material/batch combination is just the forecast quantity
3b) Effective Forecast for everything else: forecast quantity for that line - SUM(all quantites before that one for the same stock_month/country/material) + SUM (all excess quantities before that one for the same stock_month/country/material) ---> If this value is below 0, then list 0, otherwise it's the value
4) Excess calculation = Quantity - Effective forecast
How can I do this in Power BI? (In either query editor or dax)
Thank you!
