Power BI Dax - How to calculate running totals (line of balance) with a starting total

Jtbonner1986 — Thu, 15 Jun 2023 14:50:33 GMT

Hi,

I poseted something similar before but didnt get a solution that worked.

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

The calulation 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. If there is no data for a pactiular month then usage or purchases should be classes as 0 and the stock would roll over to the next month. The issue to the previous solution was when there was no data for some months the calculation would break

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

What i want to do is create a table within power bi that outputs a visual with a running total calculated like the below. I have a calendar table already created:

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

Many thanks in advance

Josh

topic Power BI Dax - How to calculate running totals (line of balance) with a starting total in DAX Commands and Tips

Power BI Dax - How to calculate running totals (line of balance) with a starting total