Stock Tracking Resetting

[yz___]

Hi All,

I am trying to currently create a stock depletion forecast, with two different excel files, both have been imported and the way it works is that one has the stock with part number, and the other has the part number, quantity required and work date. I want to create a line graph that is depleting over time, however the line keeps bouncing to its orginal number. Code and graph is listed below. I just want to display tracking over the months for individual parts. Thank You!!!

Stock Table Example

Part No.	QTY
A1	1
A1	1
B2	1

Work Order Example

Part No.	Work Date	Quantity
A1	1/1	1
A1	1/1	1
B1	1/2	1
C1	1/3	1

Measure 1

Cumulative Demand =
VAR CurrentDate = MAX ( DateTable[Date] )
RETURN
CALCULATE (
[Total Demand],
Maintenance[RepairDate] <= CurrentDate
)
Measure 2

Rolling Balance =
VAR BaseStock =
CALCULATE ( [Serviceable Stock], ALL ( DateTable ) )
RETURN
BaseStock - [Cumulative Demand]

Measure 3
Serviceable Stock =
SUMX (
FILTER ( Stock, Stock[State] = "Serviceable" ),
Stock[Qty]
)
Measure 4
Total Demand =
SUM ( Maintenance[RequiredQty] )

[v-karpurapud]

Hi @yz___ 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @yz___ 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[v-karpurapud · Accepted solution]

Hi @yz___ 

Thank you for your question on the Microsoft Fabric Community Forum.

While I’m not fully aware of the exact structure of your dataset, I’ve created a sample .pbix file to demonstrate one possible approach for modeling stock depletion over time by part, ensuring the rolling balance depletes correctly without resetting due to date hierarchies or aggregation issues.

I’ve attached a screenshot and the .pbix file illustrating this logic. Please review them to confirm whether this approach aligns with your requirements.

I hope this information is helpful. . If you have any further questions, please let us know. we can assist you further.

Regards,

Microsoft Fabric Community Support Team.
 

[danextian]

HI @yz___ 

Can you please post a sample data not matches your expected result and not something overly simplified and the reasoning behind. For example, for  Jan 3, the result is xxx because xxxxx.

[ryan_mayu]

@yz___ 

what's the expected output based on the sample data you provided?

[rohit1991]

Hii @yz___ 

The line resets because the running total is calculated over all dates, so after the last work-order date Power BI keeps evaluating extra dates and the balance appears to bounce back. The correct approach is to calculate Starting Stock – Cumulative Demand per Part up to the current date, and then return BLANK() for dates after the last demand date of that part. This keeps the running balance continuous per part and stops the line exactly at the final work-order date, preventing any reset behavior.