Invoice and Payment data model

[BiRav]

Hi everyone, 

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! 🙂 

[Anonymous]

Hi @BiRav ,

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.

Best Regards

[speedramps]

Please can you provide the example data as tables rather than screen shots.
Also name the tables so we know which is the invoive and paid table.

Also include some matching invoive payments.

You can do this using Onedrive, Dropbox or Sharepoint.
We can then import the data and build a model.
Sorry I wont type all the test data fo you.
I expect you to neatly prepare and share data if you want free voluntreer support.

Look foward to helping when the infomation is forthcoming.