How can I add dimension tables with dates to my model?

[handek]

I have dimension tables that include dates in my model.
For example, the status of a product changes every year. A product that is a new product in 2021 becomes an existing product in 2022.
Similarly, a customer who is not in 2022 becomes a new customer in 2023.

When I want to add the product or customer status information to a table visual, I get an error about model connection or some rows are duplicated.
How can I include these tables in my model? Is my model wrong?

[Chris_White]

Yes, your model is causing you to have duplicate rows. If your customer is in dim_CustomerStatus three times, then the sale will be counted three times unless you also filter dim_CustomerStatus to just one year. Same for product status. I think the best solution for you is to also join on date, but perhaps not in the way you're immediately thinking.

1. Create a new column in dim_ProductStatus called Product_status_ID and define it as [Product_ID] & [Date]
2. Create a new column in dim_CustomerStatus called Customer_status_ID and define it as [Customer_ID] & [Date]
3. Create a new column in FACT SALES called Product_status_ID defined as [Product_ID] & year([Date])
4. Create a new column in FACT SALES called Customer_status_ID defined as [Customer_ID] & year([Date])
5. Remove your existing joins: dim_Product -> FACT SALES <- dim_Customer.
6. Join dim_Product -> dim_ProductStatus -> FACT SALES <-dim_CustomerStatus <- dim_Customer. Joins to FACT SALES use the new columns you made in steps 1-4. Other two joins are as they are now.

In general, model issues are caused by tables not having a unique identifier (i.e. an ID column which uniquely idenfies each row). Two of your tables did not have such a column, so I've added one.