'Burn Down' of Payments due vs Payments Sent

[fionabee]

I have a data set which has a column of all of the payments that need to be sent (these don't have a 'due date') and a columns for the amount and date paid. 

I want to create a chart which shows over time how much is remaining of the total balance of payments to be paid based on the payment date. 

So total amount due - amount paid as at each payment date? 

[v-menakakota · Accepted solution]

Hi @fionabee ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Create Date Table:

Calendar =

ADDCOLUMNS(

    CALENDAR(

        MIN('Payments'[Payment Date]),

        MAX('Payments'[Payment Date])

    ),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "MMM"),

    "MonthNumber", MONTH([Date]),

    "Day", DAY([Date])

)
This will create a Calendar table with every date between your first and last payment dates.

Create relationship from Calender[Date]->Payments[Payment Date]

Write measure for Cumulative Paid:
Cumulative Paid =

CALCULATE(

    SUM('Payments'[Amount Paid]),

    FILTER(

        ALL('Calendar'[Date]),

        'Calendar'[Date] <= MAX('Calendar'[Date])

    )

)

Now your cumulative payment is based on Calendar[Date], not just existing payments.

Measure for total amount due:
Total Amount Due = MAX('Payments'[Amount Due])

Remaining Balance:
Remaining Balance = [Total Amount Due] - [Cumulative Paid]

Select the Line chart Visual
  X-Axis: Calendar[Date]
 Y-Axis: Remaining Balance
Please go through  the screenshot and pbix file for more information.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[v-menakakota]

Hi @fionabee ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @fionabee ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[fionabee]

Hello,

Apologies for the delay. I've just returned to this project and am getting minus figures when I use details you've provided above. Looking at your example file, the figure you've used as the amount due is just the total of the payments sent whereas in my data the amount due is a separate column e.g:

Payment ID	Amount Due	Amount Paid	Paid Date
1	300	300	01/01/2025
2	400	400	01/02/2025
3	260	0	22/03/2025
4	565	0	01/03/2025
5	123	123	18/04/2025
6	444	0	03/04/2025
7	543	0	15/05/2025
8	333	333	01/05/2025

I think my issue is that the total amount due has no correlation to any date whereas the sent amount is associated to the payment date?

[v-menakakota]

Hi @fionabee ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). 

Do not include sensitive information. Do not include anything that is unrelated to the issue or question. 

Please show the expected outcome based on the sample data you provided. 

Thank you,
Menaka.

[fionabee]

Hi Menaka, 

I've linked sample data from my one drive with a tab of what I'm trying to achieve in Power BI.

(hopefully this works?)
Example Data 

[v-menakakota]

Hi  @fionabee  ,

Using the sample data provided, I created a Payments table to replicate the scenario.
Create a Calendar table:

Calendar =ADDCOLUMNS(

    CALENDAR(

        MIN('Payments'[Paid Date]),

        MAX('Payments'[Paid Date])

    ),

    "YearMonth", FORMAT([Date], "YYYY-MM")

)
Create a relationship:
Calendar[Date] → Payments[Paid Date]

Total Amount Due

Total Amount Due = SUM('Payments'[Amount Due])


Cumulative Paid:
Cumulative Paid =

CALCULATE(

    SUM('Payments'[Amount Paid]),

    FILTER(

        ALL('Calendar'[Date]),

        'Calendar'[Date] <= MAX('Calendar'[Date])

    )

)

Remaining Balance:
Remaining Balance = [Total Amount Due] - [Cumulative Paid]

Use a Line and Clustered Column Chart:

• X-Axis: Calendar[YearMonth]
• Column Values: Cumulative Paid
• Line Values: Remaining Balance
  
  Please go through the pbix file
  
  If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
  Best Regards, 
  Menaka.
  Community Support  

[v-menakakota]

Hi @fionabee ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[fionabee]

Hello, 

This shows the remaining balance in the negative though? I don't think that can be correct?

Thanks

[v-menakakota]

Hi @fionabee ,

You're right the initial visual displayed Remaining Balance as negative due to how the combination chart rendered it. I’ve updated the DAX measure to ensure that Remaining Balance never goes below zero, and the revised chart now displays the values more intuitively, as shown below.

Please go through the pbix file:

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
Best Regards, 
Menaka.
Community Support Team  

[fionabee]

Hello, this didn't work. I'm not sure why someone else has chosen it as a solution but it didn't give me what I needed. Thanks anyway.

[grazitti_sapna]

Hi @fionabee,

In Power BI, you can create a cumulative payment measure that calculates the total amount paid up to a given point in time. This will help in calculating the remaining balance over time.

Go to the Modeling tab in Power BI and create a new measure for the cumulative payment:

Cumulative Paid =
CALCULATE(
SUM('Payments'[Amount Paid]),
FILTER(
'Payments',
'Payments'[Payment Date] <= MAX('Payments'[Payment Date])
)
)

This measure calculates the total Amount Paid up until each payment date.

Create the Remaining Balance Measure: Now, create another measure to calculate the remaining balance as the total Amount Due minus the Cumulative Paid amount.

Remaining Balance =
MAX('Payments'[Amount Due]) - [Cumulative Paid]

Now you can use this measure in your chart to get the desired output

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!