Forum Discussion
Helper IIICalculate Balance based on Date
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 |
jeronimo2334 hope attached will get you going
2 Replies
- parry2k
Super User
jeronimo2334 hope attached will get you going
MisterFryResolver III
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()