topic Re: Help to create stock inventory forecast (cumulative/running total) in DAX Commands and Tips

Help to create stock inventory forecast (cumulative/running total)

CampBI — Thu, 05 Jun 2025 05:22:39 GMT

Hi All, I would like to ask help on getting the stock inventory forecast (running total) of the data below.

The requirement should be cumulative or running total for forecast of inventory having the data for Daily qty, arrival, reserved and sales order.

Current forecast measure=

Sum of Inventory - sum(Reserved) + sum(Arrival) - sum(Sales Order)

Current Tables:

Date table (created date table)

For Reserved (for production use)

For Arrival (stock to be arrived)

For Sales Order (Order items with date)

For Forecast it is just a measure where , Sum of Inventory + Arrival - Reserved - Sales Order

Thank you.

Expected chart is line chart, but currently I only got this broken line.

Tables:

Item list table
Item	Color	Qty	Inventory(today date only)
A12	Black	34	6/5/2025
A12	Blue	25	6/5/2025
A13	Black	6	6/5/2025


Purchase Order(Arrival Table)
Item	Color	Qty	arrival Date
A12	Black	100	7/25/2025
A12	Blue	10	6/20/2025
A13	Black	16	6/30/2025

Sales Order Table
Item	Color	Qty	Date
A12	Black	3	6/11/2025
A12	Blue	47	6/22/2025
A13	Black	78	6/28/2025

Production (Reserved Table)
Item	Color	Qty	Date
A12	Black	2	6/5/2025
A12	Blue	3	6/22/2025
A13	Black	6	6/28/2025


Calendar Table(Date Table)
Calendar = CALENDAR (DATE (2024, 1, 1), DATE (2026, 12, 31))

Re: Help to create stock inventory forecast (cumulative/running total)

Nasif_Azam — Thu, 05 Jun 2025 09:18:25 GMT

Hey CampBI ,

To create a stock inventory forecast with a running total, you need to calculate a cumulative measure over dates using DAX.

Objective:
You want to create a line chart with a cumulative forecast showing:

Inventory (today) + Cumulative Arrival - Cumulative Reserved - Cumulative Sales Order

Your data includes:

Inventory table (snapshot as of today)
Arrival table (future stock delivery)
Reserved table (production)
Sales Order table (outgoing demand)
Calendar table

1. Prepare Relationships
Ensure these relationships are in place:

Calendar[Date] → Arrival[Date]
Calendar[Date] → Reserved[Date]
Calendar[Date] → SalesOrder[Date]

Use many-to-one, single direction. If your Inventory table is snapshot-based (only today's inventory), keep it disconnected or relate by item only.

2. Base Measures

Total Arrival = SUM('Arrival'[Qty])
Total Reserved = SUM('Reserved'[Qty])
Total SalesOrder = SUM('SalesOrder'[Qty])
Total Inventory = SUM('Inventory'[Qty])  -- Static today snapshot

3. Create Running Totals

Cumulative Arrival =
CALCULATE(
    [Total Arrival],
    FILTER(
        ALLSELECTED('Calendar'[Date]),
        'Calendar'[Date] <= MAX('Calendar'[Date])
    )
)

Cumulative Reserved =
CALCULATE(
    [Total Reserved],
    FILTER(
        ALLSELECTED('Calendar'[Date]),
        'Calendar'[Date] <= MAX('Calendar'[Date])
    )
)

Cumulative Sales Order =
CALCULATE(
    [Total SalesOrder],
    FILTER(
        ALLSELECTED('Calendar'[Date]),
        'Calendar'[Date] <= MAX('Calendar'[Date])
    )
)

If you want to filter by item or color, use slicers and/or add CALCULATE modifiers.

4. Create Forecast Running Total Measure

Forecast Running Total =
VAR InitialInventory = CALCULATE([Total Inventory], ALL('Calendar'))
RETURN
InitialInventory
+ [Cumulative Arrival]
- [Cumulative Reserved]
- [Cumulative Sales Order]

5. Visual Setup
Use a line chart in Power BI:

Axis: Calendar[Date]
Values: Forecast Running Total

Optional:

Add slicers for Item, Color
Use ALL('Calendar') in InitialInventory so it stays static unless filtered by Item or Color.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

Re: Help to create stock inventory forecast (cumulative/running total)

Anonymous — Tue, 10 Jun 2025 12:18:54 GMT

Hi CampBI

Thank you for reaching out microsoft fabric community forum.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: Help to create stock inventory forecast (cumulative/running total)

v-echaithra — Wed, 25 Jun 2025 10:02:16 GMT

Hi CampBI ,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

Re: Help to create stock inventory forecast (cumulative/running total)

v-echaithra — Mon, 30 Jun 2025 13:54:37 GMT

Hi CampBI ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

Re: Help to create stock inventory forecast (cumulative/running total)

CampBI — Tue, 01 Jul 2025 02:54:05 GMT

Thank you uso much!

topic Re: Help to create stock inventory forecast (cumulative/running total) in DAX Commands and Tips

Help to create stock inventory forecast (cumulative/running total)

Re: Help to create stock inventory forecast (cumulative/running total)

Objective:You want to create a line chart with a cumulative forecast showing:

Re: Help to create stock inventory forecast (cumulative/running total)

Re: Help to create stock inventory forecast (cumulative/running total)

Re: Help to create stock inventory forecast (cumulative/running total)

Re: Help to create stock inventory forecast (cumulative/running total)

Objective:
You want to create a line chart with a cumulative forecast showing: