Forum Discussion
Issue with Adding Forecast Values to Current Stock in Power BI Measure
Hi 3005mfab,
The file is correct. The problem this time is since we are using TODAY() in the measure, it's looking for 8/20 in the DailyInventory table. You only have 8/19 in your sample table so the forecast worked when I looked at the report on 8/20.
Good question though. That raises one assumption in my measure. Your data must be up to date. There must be data in DailyInventory for yesterday, whenever you look at the report.
I've tweaked the measure so that it will just work off the last date where you show inventory in DailyInventory and forecast 7 days out from that date. Which version you use depends on how you want the report to work.
If you want it to be more robust to data that isn't updated (but also show incorrect data in that case, since you're using forecast data for days that will have already happened), use the newer version. If that will never be an issue or you want it to be obvious when data is not updated, the original version will work.
Updated measure is below and updated pbix is attached:
Inventory = VAR _maxdateinventory = -- Last date with inventory MAXX ( ALL ( DailyInventory[Date Added] ), DailyInventory[Date Added] ) VAR _seldate = SELECTEDVALUE ( 'Date'[Date] ) -- Selected date VAR _sum = SUM ( 'DailyInventory'[Total Value in EUR] ) -- Current stock value VAR _lastsum = -- Most recent actual inventory CALCULATE ( SUM ( 'DailyInventory'[Total Value in EUR] ), REMOVEFILTERS ( 'Date'[Date] ), 'Date'[Date] = _maxdateinventory ) VAR _forecast = -- Future changes from AFPO, EKPO, RESB CALCULATE ( SUM ( 'AFPO'[Value in EUR] ) + SUM ( 'EKPO'[Value in EUR] ) - SUM ( 'RESB'[Value in EUR] ), REMOVEFILTERS ( 'Date' ), 'Date'[Date] > _maxdateinventory, 'Date'[Date] <= _seldate ) VAR _result = -- Forecast inventory for future dates, actual for past dates SWITCH ( TRUE(), MAX ( 'Date'[Date] ) >= _maxdateinventory && MAX ( 'Date'[Date] ) <= _maxdateinventory + 7, _lastsum + _forecast, -- Forecast for the next 7 days MAX ( 'Date'[Date] ) < _maxdateinventory, _sum -- Show current stock for past dates ) RETURN _result
Hi Wilson_ , attached the file! https://we.tl/t-RTAl1SjG8W
The projection logic should work as follows:
- Start with the Total Value in EUR for the current date (Date Added = 8/19/2024).
- For the next days, add the net values from EKPO (purchase orders) and AFPO (production orders), while subtracting the RESB (requirements) values.
- This calculation should repeat for each subsequent day, where the new projected total becomes the base value for the following day.
Here’s how you can implement this logic in Power BI:
Start with the current Total Value in EUR: Use the Daily Inventory table to retrieve the value for Date Added = 8/19/2024. This will be the starting point for the forecast.
Calculate projected values for the following days:
- Create a measure that sums the values from the EKPO, AFPO, and RESB tables, based on their respective dates.
For each future date, the calculation would be
Projected Value (for day N) = Total Value (previous day)
+ SUM(EKPO[Value] for day N)
+ SUM(AFPO[Value] for day N)
- SUM(RESB[Value] for day N)
Wilson_Memorable Member
3005mfab,
See attached pbix (image below).
The biggest issue in your original measure is there was no cumulative sum as you continue to go into future dates. Let me know if you have any questions about my updated measure.
Hi Wilson_ , thanks for your help! But did you perhaps send me the wrong file, because unfortunately the same problem persists. Because unfortunately your image is correct, but the file you attached is different. The problem is still that only the changes are displayed. I would be very grateful if you could help me with this again!
- Wilson_
Hi 3005mfab,
The file is correct. The problem this time is since we are using TODAY() in the measure, it's looking for 8/20 in the DailyInventory table. You only have 8/19 in your sample table so the forecast worked when I looked at the report on 8/20.
Good question though. That raises one assumption in my measure. Your data must be up to date. There must be data in DailyInventory for yesterday, whenever you look at the report.
I've tweaked the measure so that it will just work off the last date where you show inventory in DailyInventory and forecast 7 days out from that date. Which version you use depends on how you want the report to work.
If you want it to be more robust to data that isn't updated (but also show incorrect data in that case, since you're using forecast data for days that will have already happened), use the newer version. If that will never be an issue or you want it to be obvious when data is not updated, the original version will work.
Updated measure is below and updated pbix is attached:
Inventory = VAR _maxdateinventory = -- Last date with inventory MAXX ( ALL ( DailyInventory[Date Added] ), DailyInventory[Date Added] ) VAR _seldate = SELECTEDVALUE ( 'Date'[Date] ) -- Selected date VAR _sum = SUM ( 'DailyInventory'[Total Value in EUR] ) -- Current stock value VAR _lastsum = -- Most recent actual inventory CALCULATE ( SUM ( 'DailyInventory'[Total Value in EUR] ), REMOVEFILTERS ( 'Date'[Date] ), 'Date'[Date] = _maxdateinventory ) VAR _forecast = -- Future changes from AFPO, EKPO, RESB CALCULATE ( SUM ( 'AFPO'[Value in EUR] ) + SUM ( 'EKPO'[Value in EUR] ) - SUM ( 'RESB'[Value in EUR] ), REMOVEFILTERS ( 'Date' ), 'Date'[Date] > _maxdateinventory, 'Date'[Date] <= _seldate ) VAR _result = -- Forecast inventory for future dates, actual for past dates SWITCH ( TRUE(), MAX ( 'Date'[Date] ) >= _maxdateinventory && MAX ( 'Date'[Date] ) <= _maxdateinventory + 7, _lastsum + _forecast, -- Forecast for the next 7 days MAX ( 'Date'[Date] ) < _maxdateinventory, _sum -- Show current stock for past dates ) RETURN _result