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
Memorable Member3005mfab,
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