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