Invoices and payments data model

I'm kinda fresh to power bi and I'm struggling with the model that I want to build there.

I have 3 tables: Customers, Invoices and Payments. And I need to build a data model in Power bi (with the respect that I want to build a report after where I want to show the number of total (un)paid invoices.

My initial goal is having 3 tables (Customers as a dim and Invoices and Paymenst as fact tables, based on customer id relationship) but then I have struggles to find number of unpaid invoices.

So maybe some of you can help me with that? Maybe someone can come up with the better data model? And how to find the total number of unpaid invoices?

Here there are screenshots of the corresponding tables:

Note: in payment table there are different types of payments(including refunds) + customers can split the total number of the invoice into numerous payments.

Thanks!

• All forum topics
• Previous Topic
• Next Topic

v-yiruan-msft

• Mark as New
• Bookmark
• Subscribe
• Mute
• Subscribe to RSS Feed
• Permalink
• Print
• Report Inappropriate Content

Thanks for reaching out to us with your problem. If I understand correctly, there are 3 tables in your model: customer dimension table, payment and invoice fact table. And you created the relationship among these three tables base on the field [Customer ID]. And now you wnat to get the number of unpaid invoices. How to identify that invoice is paid or not? Is there any conditions need to fulfill? Could you please share your expected result with the specific examples base on the current provided data? Which invoice will be identified as Unpaid in the below screenshot? It would be helpful to find the solution. Thank you.

Community Support Team _ Rena
If this post helps , then please consider Accept it as the solution to help the other members find it more quickly.