Running Balance from a stream of payments

[sjrrkb123]

Need a little help on this balance calculation:

I have a stream of payments with dates and payment type that looks like the following:

Invoice		Payment Type		Date		Payment		My Balance Calc
1A1		Invoice		1/1/2020		1000		0
1A1		Cash Received		1/4/2020		-500		0
1A1		Cash Received		1/5/2020		-400		0
1A1		Write Off		1/5/2020		-100		0
1B1		Invoice		2/4/2020		2000		0
1B1		Cash Received		2/5/2020		-1500		0
1B1		Cash Received		2/20/2020		-250		0
1B1		Cash Received		2/21/2020		-200		0
1B1		Cash Received		3/1/2020		-50		0

Here is what I want:

Invoice		Payment Type		Date		Balance
1A1		Invoice		1/1/2020		1000
1A1		Cash Received		1/4/2020		500
1A1		Cash Received		1/5/2020		100
1A1		Write Off		1/5/2020		0
1B1		Invoice		2/4/2020		2000
1B1		Cash Received		2/5/2020		500
1B1		Cash Received		2/20/2020		250
1B1		Cash Received		2/21/2020		50
1B1		Cash Received		3/1/2020		0

The current dax formula I am using is this:
Calculate(SUM('Date'[Payment]), FILTER('Data', 'Data'[Invoice] = Earlier('Data'[Invoice]) && 'Data'Date <= 'Data'Date))
however, this just gives the remaining balance after all the payments are added on a given invoice.

I will attach a sample .pbix as well

[v-janeyg-msft · Accepted solution]

Hi, @sjrrkb123 

According to your description, I think you can create a index column in PQ first, then use it to create a measure.

Like this:

Measure = SUMX(FILTER(ALL('Current State'),[Index]<=SELECTEDVALUE('Current State'[Index])),[Payment])

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sjrrkb123]

@v-janeyg-msft while this does work for the dataset provided, the actual one is around 800k rows long and this runs extremely slow. 

Any thoughts on how to do this as a calculated column instead or reduce the overhead on the measure?

[v-janeyg-msft]

Hi, @sjrrkb123 

Try the calculated column: 

Column = SUMX(FILTER('Current State',[Index]<=EARLIER('Current State'[Index])),[Payment])

Best Regards

Janey Guo

[sjrrkb123]

Here is link to the power bi report: https://drive.google.com/file/d/1p0a3tbsKTMZ805nwd5QsdRpBJuqb7Tto/view?usp=sharing