Relationship Date Table

[josevaras]

Hello - thanks in advance.

• I have two products table with product information. One is Product Sales Actuals and the other is for Product Sales Forecast.
• Both tables have a Reporting Weeks column with dates. I want to set a relationship using Reporting Week but I dont want a Many to Many.
• So what I did was create another table with our Reporting Weeks (FY, FM, etc) and then did a 1:1 relationship from the New Table to each of the other tables, the Product Sales Actual and Product Sales Forecast
• The problem I am having is that the data returned is wrong, depending on which table I use to add a filter. Example, if I go to the Product Sales Actuals table and bring in color, I get the correct value. However the Product Sales Budget number is an aggregation, not the actual number. The same thing happen if I do the opposite.
  
  

Any ideas on what I am doing wrong or resources I can go to for help? I am not able to upload the pbix as it contains confidential data. Thanks

[v-xuding-msft · Accepted solution]

Hi @josevaras ,

Can you please share a few photos about the sample data and your expected results? Those will make us understand clearly about your requirement.

How to Get Your Question Answered Quickly

Best Regards,

Xue Ding

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

[amitchandak]

When you created a reporting week table. I have created a normal date table and used all dates that cover both tables. Or just taken distinct from one table.

Share some sample data and expected outcome. The data should able to show issues you are facing.