Automating Financial Records

[PoewrBIUser]

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

[danextian]

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.

[PoewrBIUser]

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? 

[danextian · Accepted 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.