Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a problem that I can't seem to get my head around.
Requirement: Automate the payment breakdown for lumpsum payments.
Payment Structure
Payment | Amount |
Month 1 | 340 |
Month 2 | 340 |
Month 3 | 340 |
Month 4 | 340 |
Month 5 | 340 |
Month 6 | 340 |
Month 7 | 340 |
Month 8 | 340 |
Month 9 | 340 |
Month 10 | 340 |
Month 11 | 340 |
Month 12 | 260 |
Payment Record
Date | Payment |
1/14/2021 | 1800 |
7/27/2021 | 1800 |
12/5/2021 | 400 |
Question
1. How do I make sure that the payment for the month be reflected in the dashboard
2. How do I show that for the payment for month 6 an 11 has not yet been paid fully, as shown below:
Payment | Amount | Cumulative Amount | Payment 1 (1800) | Payment 2 (1800) | Payment 3 (400) |
Month 1 | 340 | 340 | Paid on time | ||
Month 2 | 340 | 680 | Paid on time | ||
Month 3 | 340 | 1020 | Paid on time | ||
Month 4 | 340 | 1360 | Paid on time | ||
Month 5 | 340 | 1700 | Paid on time | ||
Month 6 | 340 | 2040 | 100 paid | 240 late payment | |
Month 7 | 340 | 2380 | Paid on time | ||
Month 8 | 340 | 2720 | Paid on time | ||
Month 9 | 340 | 3060 | Paid on time | ||
Month 10 | 340 | 3400 | Paid on time | ||
Month 11 | 340 | 3740 | 200 paid | 140 late payment | |
Month 12 | 260 | 4000 | Paid on time |
Solved! Go to Solution.
As mentioned, the solution is solely based on the sample data. If there are multiple payments within the same month, the lookupvalue formula needs to be tweaked and some other calculations as well. Please see attached modified pbix.
Proud to be a Super User!
Hi @PoewrBIUser ,
If you want to achieve what you are expecting, it is a rather tricky one and will require a lot of brain cells.
Below is the closest I could get to.
The solution requires a lot of calculated column and solely based on the provided data. Please see atttached pbix for your reference.
Proud to be a Super User!
Hello @danextian, thank you for your reply,
But I don't see this working for someone who made 2 payments in the same month, are there any other possible solution?
As mentioned, the solution is solely based on the sample data. If there are multiple payments within the same month, the lookupvalue formula needs to be tweaked and some other calculations as well. Please see attached modified pbix.
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |