Forum Discussion
Running balance (difference) by groups
I'm trying to calculate running balance for quantity on hand after usage.
My sample table looks like this. Any idea on what I need to do to get the expected value?
| Index 1 | Index 2 | Component | Start Date | On hand | Qty required | Running Balance (expected value) |
| 1 | 1 | A | 1/1/2024 | 10 | 2 | 8 |
| 2 | 2 | A | 1/2/2024 | 10 | 2 | 6 |
| 3 | 1 | B | 1/1/2024 | 5 | 1 | 4 |
| 4 | 2 | B | 1/2/2024 | 5 | 1 | 3 |
| 5 | 1 | C | 1/1/2024 | 15 | 1 | 14 |
| 6 | 2 | C | 1/2/2024 | 15 | 1 | 13 |
| 7 | 1 | D | 1/1/2024 | 10 | 2 | 8 |
| 8 | 1 | E | 1/1/2024 | 5 | 1 | 4 |
| 9 | 2 | E | 1/2/2024 | 5 | 1 | 3 |
| 10 | 3 | E | 1/3/2024 | 5 | 1 | 2 |
Why is On hand repeated? Shouldn't that be only listed for the first date?
Measure:
Running Balance = var c = SELECTEDVALUE('Table'[Component]) var i = SELECTEDVALUE('Table'[Index 2]) var b = filter(ALLSELECTED('Table'),[Component]=c && [Index 2]<=i) return maxx(b,[On hand])-sumx(b,[Qty required])
2 Replies
Why is On hand repeated? Shouldn't that be only listed for the first date?
Measure:
Running Balance = var c = SELECTEDVALUE('Table'[Component]) var i = SELECTEDVALUE('Table'[Index 2]) var b = filter(ALLSELECTED('Table'),[Component]=c && [Index 2]<=i) return maxx(b,[On hand])-sumx(b,[Qty required])Running Balance =
CALCULATE(
MAX(SampleTable[On hand]) - SUMX(
FILTER(
SampleTable,
SampleTable[Component] = EARLIER(SampleTable[Component]) &&
SampleTable[Start Date] <= EARLIER(SampleTable[Start Date])
),
SampleTable[Qty required]
)
)
