Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Future Inventory Forecasting in Power BI

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

28135468/14/20241282
28135458/14/20241131
28135438/14/20241417
............

EKPO :

Material PO Quantity Deliv. Date Value

280312388/14/2024223
2708471128/14/20244548
265153938/14/2024195
............

 

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!

Need Help
Tips and Tricks

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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