How to calculate a running balance (totals) Power BI

I posted something similar before but didn't get a solution that worked.

I am trying to get a predicted running stock total. I have 3 dimensions [Current Stock], [Usage], [Purchases]

The calculation for month 1 is ([Current Stock] + [purchases (for month 1]) - [usage (for month 1)]

Then the following month the calculation will use the previous months output and then calculate the current months purchases - the current month usage.

This is the important bit of the calculation and where I am having trouble: If there is no data for a particular month (usage & or purchases) then usage or purchases should be classed as 0. So the stock levels would roll over to the next month.

Example data and example outputs are below:

Current Stock;

Material	Stock on Hand

A          100

B          500

C          1000

Purchases

Material	PurchDate	Quantity

A    	01/01/2023	10

A    	01/02/2023	20

A    	01/04/2023	30

B    	01/01/2023	10

B    	01/02/2023	20

B    	01/04/2023	15

Usage

Material	UseageDate	 Quantity

A       01/01/2023	    50

A   	01/02/2023  	10

A   	01/04/2023	    20

B    	01/01/2023	    10

B    	01/02/2023   	30
 
B    	01/04/2023	    40

Desired output

| Material  |01/01/2023 |  01/02/2023 |  01/03/2023 |  01/04/2023 |

| A         | 60        |70         |70         |80         |


| B         | 410	    |420        |420        |395        |

(Stock on hand + Purch) - Usage(Previous month LoB + Purchases) - usage

hope you can help

Try this in powerquery (m)

let StartDate=List.Min(Purchases[PurchDate]&Usage[UseageDate]),
EndDate=List.Max(Purchases[PurchDate]&Usage[UseageDate]),
dates=List.Distinct(List.Transform({Number.From(StartDate)..Number.From(EndDate)}, each Date.StartOfMonth(Date.From(_)))),
#"Added Custom" = Table.AddColumn(Table.FromList(List.Distinct(Stock[Column1] & Purchases[Material] &Usage[Material]),null, {"Material"}), "Custom", each dates),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom","Stock",(x)=>List.Sum(Table.SelectRows(Stock, each x[Custom]=StartDate and [Column1]=x[Material])[Column2])??0),
#"Added Custom2" = Table.AddColumn(#"Added Custom1","Purchases",(x)=>List.Sum(Table.SelectRows(Purchases, each [Material]=x[Material] and [PurchDate]=x[Custom])[Quantity])??0),
#"Added Custom3" = Table.AddColumn(#"Added Custom2","Usage",(x)=>List.Sum(Table.SelectRows(Usage, each [Material]=x[Material] and [UseageDate]=x[Custom])[Quantity])??0),
#"Grouped Rows" = Table.Group(#"Added Custom3", {"Material"}, {{"data", each 
    let #"Added Index" = Table.AddIndexColumn(Table.Sort(_,{{"Custom", Order.Ascending}}), "Index", 0, 1, Int64.Type),
    #"Added Cum Total" = Table.AddColumn(#"Added Index", "StockCum", each List.Sum(List.FirstN(#"Added Index"[Stock],[Index]+1))),
    #"Added Cum Total2" = Table.AddColumn(#"Added Cum Total", "UsageCum", each List.Sum(List.FirstN(#"Added Index"[Usage],[Index]+1))),
    #"Added Cum Total3" = Table.AddColumn(#"Added Cum Total2", "PurchasesCum", each List.Sum(List.FirstN(#"Added Index"[Purchases],[Index]+1))),
    #"AddBalance" = Table.AddColumn(#"Added Cum Total3","EndingBalance", each [StockCum]-[UsageCum]+[PurchasesCum], type number)
    in #"AddBalance"
, type table [Custom=nullable date, EndingBalance=number] }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Custom", "EndingBalance"}, {"Date", "EndingBalance"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded data", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded data", {{"Date", type text}}, "en-US")[Date]), "Date", "EndingBalance", List.Sum)
in  #"Pivoted Column"