Balance calculation as of selected date

[Anonymous]

Need help with a calculation for the balance amount for the customer as of the selected date. 

The invoice details and the payment date for the invoices are in the below table 

Customer	Invoice	Trans Date	Amount	Paid	Payment Date	Invoice Bal
1	100	2/2/2023	500	450	3/3/2023	50
1	110	2/20/2023	200	200	2/25/2023	0
2	115	3/10/2023	400	0		400

Report need to show the customer with their balance as of the date selected and the balance as of current date

If the selected calendar date is 02/28/2023 , and current date is 03/10/2023 expected result is 

Customer	Balance	Current Balance
1	500	50
2		400

[Anonymous]

Hi @Anonymous ,

According to your description, it seems that you want to display the value on the visual base on the date slicer dynamically. But how to calculate it when selected the date is 02/28/2023? It got the values which the payment data is greater than 02/28/2023 and lower than 03/10/2023? Why the current date 03/10/2023? Could you please explain the calculation logic base on the provided sample data? It would be helpful to find the solution. Thank you.

Customer	Balance	Current Balance
1	500	50
2		400

Best Regards

[Anonymous]

@Anonymous 

I did not do a good job in explaining the above and also there is a major tweak also to the data. 

The base requirement is to provide the balance o

n customer for the date selected. Balance is derived based on the invoices less any receipts. Also need the column for the balance for the month 

Here is the invoice table 

Customer	Invoice	Invoice Date	Value
CUS1	INV-L1	8/20/2023	100
CUS1	INV-L2	8/20/2023	150
CUS1	INV-1	9/1/2023	500
CUS1	INV-2	9/15/2023	1000
CUS1	INV-3	9/25/2023	2000
CUS5	INV-6	10/4/2023	1100

Receipts table - The data can have an advance payment also which should be reflected in the balance

Customer	Invoice	Rcpt Date	Value
CUS1	INV-L1	9/5/2023	-100
CUS1	INV-1	9/1/2023	-200
CUS1	INV-1	9/29/2023	-200
CUS1	INV-2	9/30/2023	-1000
CUS1	INV-1	10/2/2023	-50
CUS2	ADV-1	9/20/2023	-250

If date selected is Aug 31st, expected result 

Customer	Balance	Current Month
CUS1	250	250

If date selected is Sep 30th, expected result

Customer	Balance	Current Month
CUS1	2250	2100
CUS2	-250

For CUS1 , balance of 2250 is INV-L2 (100) + INV-1 (100)  + INV-3 (2000)

Current balance is balance for SEP invoices -   INV-1 (100)  + INV-3 (2000)

Pbix is attached 

https://drive.google.com/file/d/1k4aGruU1ObhUurwYHRocYP5QC0Tj2vfq/view?usp=sharing