Does anyone have a recommendation for dealing with many to many relationships and dates?

[mds123]

The tables I have are:

• A generic date table that lists every date and what year, quarter, month it's in.
• An item master table that lists the individual items we sell.
• A daily order table that lists the date and what items were sold.
• A budget table that lists the item, month, and how many we would like to sell in that in that month. 

My goal is to compare actuals vs budget. However when I try to establish relationships with the date table for the budget table I get a many to many relationship and I worry about introducing duplicates and having wrong calculations. I'm guessing this is a common task people use Power Bi to solve, does anyone have a recommendation? 

[ray_aramburo]

Date to Budget is a 1 to many relationship, it shouldn't be many to many. Check if you have any duplicate dates on the date table and delete them through Power Query. 

[mds123]

The budget is by the month not the individual date so it causes many to many. 

[ray_aramburo · Accepted solution]

If you are joining by the name of the month, then yes, you'll get that kind of relationship. I would suggest you in that case to create a generic end/start date for your months, for example, January -> 01/01/2023 or 31/01/2023 and use that column as the key to the relationship. My recommendation is to add that date from the source, if not possible, I can share you some tips on how to create it from scratch from Power Query.