Calculate Balance based on Date

[jeronimo2334]

Hi all,

So I have 3 tables, one with customers, one with customers' charges and one with customers' payments. I am trying to figure out a good way to get a balance for each customer. The user wants to select a date from a date picker and the table should return what the total balance up to that day was.

Any recommendations are welcome.

customerId	Name
1	alpha
2	beta
3	gamma
4	delta
5	phi

customerId	date	charge
1	9/23/2018	5
2	9/23/2018	12
1	9/25/2018	13
3	9/25/2018	14
3	9/25/2018	41
5	9/27/2018	22
5	9/27/2018	17

customerId	date	payment
1	9/12/2018	35
2	9/12/2018	23
1	9/16/2018	13
3	9/18/2018	14
3	9/25/2018	41
5	9/23/2018	2
5	9/23/2018	17

[parry2k · Accepted solution]

@jeronimo2334 hope attached will get you going

[MisterFry]

You could create a disconnected date slicer that allows the user to select a date, detect that date via SELECTEDVALUE(), and use Balance = CALCULATE(SUM(CREDITS), date <= SELECTEDVALUE([DisconectedDateField])) - CALCULATE(SUM(DEBITS), date <= SELECTEDVALUE([DisconectedDateField])) 

You could generate the list of dates by either doing a SUMMARIZE() on a concatenation of your other table's date fields, or you could autogenerate them in power query using List.Generate()