Unable to solve, product code mapping related problem.

[Ab_2023]

I have common problem however unable to solve using power query or DAX

Have a sales table in SQL which has product code, country of sales, day of transaction and sales amount, doesnt cotain product name. For product name we have product code mapping table in excel. 

Issue is after certain date the product names gets changed. We need to plot product name wise sales with the dates.

Sales table

Country	Product Code	Date	Sales
India	3256	2/1/2019	8,023
China	9558	12/1/2019	6,541
India	9558	2/1/2020	7,058
China	9558	9/1/2020	8,165
India	9558	9/1/2021	6,739
China	9558	10/1/2021	7,343
India	3256	11/1/2021	7,519
China	3256	4/1/2022	6,944
India	3256	11/1/2022	7,787
China	9558	1/1/2023	8,060
India	9558	2/1/2023	6,400

Mapping Table in Excel

Product Code	Product name	Product name after 9/1/2021
9558	Product A	Product M
3256	Product C	Product N

Would you please help me solve this.

Thanks

[some_bih]

Hi @Ab_2023 if you want to trace changing names for product you should provide inputs like StarDate and EndDate for each product code, depending on inputs (modeling this part in overall model) solution could be achived.

The best would be that in separate process you check if product is changed name and decide what is product name for current /alwasy and that value consider in model.

[some_bih]

Hi @Ab_2023 you need to create relationship in Power BI to connect your to tables (by Product code) to leverage it later using measure

Check link for this

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships 

Maintening product names in Product table should resolve your issues.

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

[Ab_2023]

Hi @some_bih, Thank you for promt response, I have already created relationship between tables, however challenges comes while writing measures because of changing names after certain dates. Could you share your Dax to resolve the issue. Thanks