Issue with Adding Forecast Values to Current Stock in Power BI Measure

[Anonymous]

Hi everyone,

I'm working on a Power BI dashboard to forecast future inventory. I have the relationships set up correctly, and I'm using the following measure to calculate the inventory forecast:

Inventory  =
VAR _sum = SUM('DailyInventory_line (2)'[Total Value in EUR]) -- Current stock value
VAR _else = SUM('AFPO'[Value]) + SUM('EKPO'[Value]) - SUM('RESB'[Value]) -- Future changes from AFPO, EKPO, RESB
VAR _forecast = CALCULATE(SUM('DailyInventory_line (2)'[Total Value in EUR]), DATEADD('Date'[Date], -1, MONTH)) -- Forecast for previous month
RETURN
SWITCH(
TRUE(),
MAX('Date'[Date]) >= TODAY() && MAX('Date'[Date]) <= TODAY() + 7, _forecast + _else, -- Forecast for the next 7 days
MAX('Date'[Date]) < TODAY(), _sum -- Show current stock for past dates
)

The measure shows the changes (i.e., the sum of AFPO, EKPO, and RESB) in the graph as expected. However, I want the measure to add these future changes (_else) to the current stock value (_sum), but it’s not doing that. Instead, it seems to replace the current stock with the forecast values rather than summing them together.

The tables I am using:

• AFPO (Production Orders):

  • Columns: Material Number, Basic Finish Date, Quantity, Value

• EKPO (Purchase Orders):

  • Columns: Material, PO Quantity, Delivery Date, Value

• RESB (Requirements):

  • Columns: Material, Requirements Date, Quantity, Value

• DailyInventory_line (Current Inventory):

  • Columns: Material, Material Description, Date Added, Value
• Date (Date Table):
  • Columns: Date

Could anyone point out why the sum of future changes isn’t being added to the current stock and how I can fix this?

Thanks in advance! 

[Anonymous]

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:

1. 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.

2. 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_]

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.

[Anonymous]

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_ · Accepted solution]

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

[Wilson_]

Hi 3005mfab,

Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂