Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Hi @Pbi07 ,
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
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
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |