Relationships

[ChrisDurham]

Good Morning, I am trying to generate a budget vs actual report but can;t figure out how to set the relationships to work. I have the following structure :-

I have a customer table with customer details in, a budget table with a budget date, amount and customerID and an invoice table with invoice date, amount and customerID. What I want to be able to do is create a table with customer name on the left, months and years across the top and then the sum of invoices and the sum of budget amounts for each customer and each period. Clearly I need to link the customerID to both the budget and invoice table, but then I also need to make sure that I display only budgets and invoices from the same time period. What is the best way to achieve this ?

[MattAllington · Accepted solution]

in short 
Delete the relationship between budget and invoices

Get a calendar table and join to budget and invoices

use the customers table and calendar table to slice and dice your visuals

write measures over your budget and invoices table

[amitchandak · Accepted solution]

@ChrisDurham , Do not Join, Budget and invoice, Create common tables like date or any other dimension join both tables with that.

https://www.seerinteractive.com/blog/join-many-many-power-bi/

https://www.youtube.com/watch?v=Bkf35Roman8

Try to be in star schema - https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

[MattAllington · Accepted solution]

in short 
Delete the relationship between budget and invoices

Get a calendar table and join to budget and invoices

use the customers table and calendar table to slice and dice your visuals

write measures over your budget and invoices table