Relationship Between Dates, Budgets and Customers

[Danielnir]

Hi, I've got three tables:

1. Sales Data:

   a) Date of transaction (format "dd/mm/yyy")

   b) Customer Name ID

   c) Invoice Value

2. Date Dimension

   a) Dates (in format "dd/mm/yyyy")

3. Budgets

    a) Date (format "dd/mm/yyy")

    b)Customer Name ID

    c) Customer Bins (There are our top performing customers listed by their name and all remaining are named as "Others")

I need to make a relationship between Dates and the customers to show budgets and summarised invoice values. The problem is in the way how the Customer Bins column is working. Some customers are changing their category from year to year as they may progress. So the same customer may be in the "Others" category in one year and by its name in another year. Therefore budget is set for "Others" column but the customers under it change from year to year. Any idea how to show budgets and sales value for the "Other" category while maintaining the list of customers that are in that year under that category? 

I can't set relationships between those datasets because it would introduce ambiguity either between dates or customers. 

Any idea how to sort this problem?

Hope I made myself clear as I can't upload the datasets here. 

Kind regards,

Daniel

[NickolajJessen]

When asking a question about your relationships, it's a good idea to include a screenshot of your relationships.

Nevertheless i'd say you need a Customer Dimensions table.

Can you be more specific about your reporting needs? If customerX is big enough to be specifically named, do you need to be able to go back to ex. 2015, where customerX was under the category "Other" and have budget & sales under this category?

Also where is your budgetvalue coming from?

[Danielnir]

Hi, thanks for a quick reply. In the below picture you can see everything available. Dates need to filter budgets and sales data and at the same time budgets need to filter sales data which introduces ambiguity. The customer dimension is not helping here as well. 

[NickolajJessen]

@Danielnir The Relationship between Budgets and Transactions seems VERY weird to me.

This is two transactiontables connected to each other in a many to many. Not good. And it's connected to a third transaction table. Not good at all! Check out Avi Singh on Data model relationships on Youtube!

Usually a data model looks something like this.

Having none of those bidirectional many to many relationships

[Dhrutivyasa-070]

Hello, 

Deniel

First of you can try in power query editor nd just append all those three table. like all the date coloum name shoud be same. same as customer name should be same. and in all three table you need create a one extra coloum that is used for determination, like in first table you add conditional colum sale data.

nd after completed this append process you add a matrix in visualization.....

Try it..

hope you understand Daniel.....