Forum Discussion
Starting and Ending Balance
I am trying to create the following in power bi.
Current date. I have inventory on hand(starting balance)-requirements+po=ending balance
Next day. Previous days ending balance needs to be the starting balance. Then Starting balance-requirements+po=ending balance. I need this pattern. The current day works , but I am having trouble for the following date starting balance being previous day ending balance. How can I make this work. I am using a matrix table, with item in rows, date in columns, and inv,requirements,po in the value columns.
| 2024-01-16 | 2024-01-17 | 2024-01-18 | ||||||||||
| Item | Inv Start Balance | Requirement | PO | Final Balance | Inv Start Balance | Requirement | PO | Final Balance | Inv Start Balance | Requirement | PO | Final Balance |
| A | 100 | 20 | 50 | 130 | 130 | 40 | 10 | 100 | 100 | 90 | 10 | 20 |
| B | ||||||||||||
| C | ||||||||||||
| D |
1 Reply
Kevin454 , You need to create cumulative measures. Take the first inventory and rest need to build on
example
Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month],sum(Table[Intial Inventory])),all('Date'))+ CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=maxx(date,date[date])))
- CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <=maxx(date,date[date])))
Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw
