Future Inventory Forecasting in Power BI

[Anonymous]

Hello everyone,

I’m working on a Power BI report to forecast future inventory values based on current inventory, purchase orders, production orders and requirements. I’ve encountered a challenge in creating a DAX measure that accurately predicts the inventory value for the next 7 days. I would appreciate any guidance or suggestions you could offer.

Problem Description:

I have the following tables:

• 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

Objective: I need to calculate the future inventory value for each day over the next 7 days.

I have the table DailyInventory_line (2), which gives me the current stock value. Based on this, I want to calculate the future inventory values by adding or subtracting data from the AFPO (production orders), EKPO (purchase orders) and RESB (requirements) tables. Here is my problem:

Initial situation:
The starting value is today's inventory value from the DailyInventory_line (2) table, which is calculated as follows for today (e.g. 14.08.2024):

Inventory =
VAR _today = TODAY()
VAR _endDate = _today + 7

VAR _initialValue =
CALCULATE(
SUM('DailyInventory_line (2)'[Total Value in EUR]),
'Date'[Date] = _today
)

This initial value is the total value in EUR on today's date.

Example:
I have the following data for 14.08.2024:

AFPO :

Material Number Basic Finish Date Qty Value

2813546	8/14/2024	1	282
2813545	8/14/2024	1	131
2813543	8/14/2024	1	417
...	...	...	...

EKPO :

Material PO Quantity Deliv. Date Value

2803123	8	8/14/2024	223
2708471	12	8/14/2024	4548
2651539	3	8/14/2024	195
...	...	...	...

RESB:
For 14.08.2024 I have a total value of 413,647.

Calculation of future stocks:
Stock value for 14.08.2024:

SUM('AFPO'[Value])+SUM('EKPO'[Value])-SUM('RESB'[Value])

In my example:

AFPO: 830
EKPO: 52,975
RESB: 413,647

Result Sum of EKPO AFPO and RESB for 14.08:

Total Value = 830 + 52,975 - 413,647 = -359,842

Value for 15.08.2024:

Starting value: The total value of the previous day (14.08), i.e. EUR 17,848,219.
Calculation for 15.08.:

New Total Value = 17,848,219 + (-359,842) = 17,488,377

Then: add or subtract values from AFPO, EKPO and RESB for 15.08 to calculate the Total Value for 16.08.
This logic is repeated for each of the following days, with the changes for each day (from the AFPO, EKPO and RESB tables) affecting the following day's stock.

What I need:
I am looking for a way to map this daily adjustment of stocks over a period of 7 days (from the current date) in a DAX measure. Today's stock value should be used as the starting point, and the values from the AFPO, EKPO and RESB tables should be calculated dynamically for the following days.

Does anyone have any ideas on how I can implement this correctly?

Many thanks in advance for your help!

[amitchandak]

@Anonymous , You need have common date and Material table and then you should be able to use the Measure you need across tables

example , table can be different in this case in each calculate

Inventory / OnHand =
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),filter(all(date),date[date] <min(date[date]))) // only first value 

+
CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

or refer

https://radacad.com/calculating-stock-on-hand-using-dax-power-bi-inventory-model