Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PoewrBIUser
New Member

Automating Financial Records

I have a problem that I can't seem to get my head around.

Requirement: Automate the payment breakdown for lumpsum payments.

 

Payment Structure

PaymentAmount
Month 1340
Month 2340
Month 3340
Month 4340
Month 5340
Month 6340
Month 7340
Month 8340
Month 9340
Month 10340
Month 11340
Month 12260

 

Payment Record

DatePayment
1/14/20211800
7/27/20211800
12/5/2021400

 

 

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:

 

PaymentAmountCumulative AmountPayment 1 (1800)Payment 2 (1800)Payment 3 (400)
Month 1340340Paid on time  
Month 2340680Paid on time  
Month 33401020Paid on time  
Month 43401360Paid on time  
Month 53401700Paid on time  
Month 63402040100 paid240 late payment 
Month 73402380 Paid on time 
Month 83402720 Paid on time 
Month 93403060 Paid on time 
Month 103403400 Paid on time 
Month 113403740 200 paid140 late payment
Month 122604000  Paid on time

 

1 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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

3 REPLIES 3
danextian
Super User
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.

danextian_0-1666677030196.png

 

The solution requires a lot of calculated column and solely based on the provided data. Please see atttached pbix for your reference.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.