Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 S...
balance
running
  • lbendlin's avatar
    lbendlin
    1 year ago

    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])
Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
1 year ago

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]
)
)