Dashboard of the future development of the inventory

[3005mfab]

Hello everyone,

I need help creating a dashboard in Power BI that forecasts future inventory based on my current daily inventory. I have the following tables available:

Daily Inventory table (current inventory) with the columns:

Material
Material Description
Date Added
Total Value
Total Value in EUR
SLoc

EKPO table (purchase orders), which increase the stock, with the columns:

Pur. Doc.
Item
Material
PO Quantity
Plnt
Deliv. Date
Scheduled Qty

RESB table (requirements) that reduce the stock, with the columns:

Material
Plant
Storage Location
Requirements date
Requirement Quantity

AFPO table (production orders) that increase the stock, with the columns:

Order
Material Number
Planned order qty
Basic finish date

What is the best way to join these tables and create corresponding measures? Are there any recommendations for DAX formulas or visualizations that are particularly helpful here?

Thank you in advance for your support!

[3005mfab]

1. Daily Inventory Table (current inventory) with the columns:

   Material Material Description Date Added Total Value Total Value in EUR SLoc Identifier

   123456

   Bolt M10x50 ST 10.9 ZP

   Tuesday, August 1, 2024

   20

   18.75

   600

   123456600

2. EKPO Table (purchase orders) that will increase the inventory, with the columns:

   Pur. Doc. Item Material PO Quantity Plnt Deliv. Date Scheduled Qty

   90543210

   00020

   9876543

   10.000

   2410

   08/25/2024

   10.000

3. RESB Table (requirements) that will decrease the inventory, with the columns:

   Material Plant Storage Location Requirements date Requirement Quantity

   3456789

   2410

   0600

   09/01/2024

   2

4. AFPO Table (production orders) that will increase the inventory, with the columns:

   Order Material Number Planned order qty Basic finish date

   67583920

   9876543

   5

   09/10/2024

Additionally, the stock values are in the MBEW Table, which looks like this:

Material Valuation Area Valuation Type Del. flag val. type Total Stock Total Value Price control Moving price

0000102

2311

0.000

0.000

0.00

V

0.01

Based on these tables, I would like to create a line chart where the x-axis represents the date and the y-axis represents the expected inventory.

[v-linhuizh-msft · Accepted solution]

Thanks for NaveenGandhi's concern about this case.

Hi @3005mfab ,

I added to the data you gave me and implemented a July-based inventory forecast. It's possible that the data I've created and the metrics will deviate from what you have, and you can take my ideas and optimise them. Here is my solution:

1.Create simple data, the specific data and relationships you can see in the .pbix file I gave you.

 

In the meantime, on the basis of the data you have given, I have also created a table of dates from 7.1 to 10.1, which I will project for August and September.

 

2.Create a measure:

Inventory =
VAR _sum =
    SUM ( 'Daily Inventory'[Total Value] )
VAR _else =
    SUM ( 'AFPO'[Planned order qty] ) + SUM ( 'EKPO'[Scheduled Qty] )
        - SUM ( 'RESB'[Requirement Quantity] )
VAR _forecast_aug =
    CALCULATE (
        SUM ( 'Daily Inventory'[Total Value] ),
        DATEADD ( 'Date'[Column1], -1, MONTH )
    )
VAR _forecast_sep =
    CALCULATE (
        SUM ( 'Daily Inventory'[Total Value] ),
        DATEADD ( 'Date'[Column1], -2, MONTH )
    )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Date'[Column1] ) > DATE ( 2024, 8, 1 )
            && MAX ( 'Date'[Column1] ) < DATE ( 2024, 9, 1 ), _forecast_aug + _else,
        MAX ( 'Date'[Column1] ) >= DATE ( 2024, 9, 1 )
            && MAX ( 'Date'[Column1] ) < DATE ( 2024, 10, 1 ), _forecast_sep + _else,
        MAX ( 'Date'[Column1] ) <= DATE ( 2024, 8, 1 ), _sum
    )

Using July's inventory to forecast August and September's, plus additional increases and decreases, a forecast can be achieved.

3.The final reslut is as follows:

 

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[3005mfab]

Hi Zhu @v-linhuizh-msft , 

Thank you very much for your help so far! Since I wanted to know the value in EUR, I tried to adjust the measure.

Inventory =

var _sum=SUM('DailyInventory_line (2)'[Total Value in EUR])

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

var _forecast_aug=CALCULATE(SUM('DailyInventory_line (2)'[Total Value in EUR]),DATEADD('Date'[Date],-1,MONTH))RETURN

SWITCH(TRUE(),

MAX('Date'[Date])>DATE(2024,8,1)&&MAX('Date'[Date])<DATE(2024,9,1),_forecast_aug+_else,

MAX('Date'[Date])<=DATE(2024,8,1),_sum)

What I get now is the following graph. The numbers are now correct when you consider the addition of AFPO, EKPO and RESB. Do you have any idea why it shows me, for example, -0.10M instead of 17.6M (17.7-0.10M = 17.6M)? Could you give me some more help as to what could be the reason for this? please find attached a screenshot.

[NaveenGandhi]

Hi @3005mfab 

Please post sample data, expected output along with screenshots to understand the problem better.

Regards,