Product metadata changes over time

[Anonymous]

Hi,

I’m having a problem with metadata that is differs per date range. To explain it as simple as possible:

I have one date table

Date

1-1-2018

1-2-2018

1-3-2018

x

I have one sales table that contains millions of rows. It contains the date of the sale, the ID of the product. The sales date column is linked to the date column of the date table.

Sales date	Product ID	Sales
1-1-2016	A	20.00
1-2-2017	B	30.00
1-3-2018	C	40.00

Then I have a product information table that contains loads of columns with all kinds of product info. The Product ID of this table is linked to the Product ID column of the sales table.

Product ID	Product Name	Brand	Manufacturer
A	Soft Drink		Coca Cola
B	Car		Tesla
C	Phone		Samsung
x

Using this setup I can create any report I want, which is perfect. It particularly helps me in calculating the amount share by manufacturer.

However…

In my industry, brands often swap manufacturers as they get sold. That way, it could be that in 2017 Product ID A was manufactured by Coca Cola, where in 2018 it was manufacturer by Samsung. I can update the manufacturer in my product information table, but that means that all sales from Product A in 2017 are suddenly also assigned to Samsung, and that affects all the share data and all my year over year data.

As a solution I thought of adding an ‘end date’ (or start date can work as well) column to the product information table. That end date specifies when a certain Product ID was last assigned to a specific manufacturer. By default for all products it will be set to 2099.

Product ID	End date	Product Name	Manufacturer
A	2017-31-12	Soft Drink	Coca Cola
A	2099-31-12	Soft Drink	Samsung
B	2099-31-12	Car	Tesla
C	2099-31-12	Phone	Samsung
x

However, I don’t know how to progress with this. There needs to be some kind of calculation in Powerquery in the sales table that’s checking the sales date and the end date and based on that decides which product ID to select.

Next to that, when I would proceed with this solution my Product ID list will contain all kinds of duplicates, and the Product ID column really should only show unique ID’s.

Would someone have any advice on how to work with this problem?

[v-shex-msft · Accepted solution]

HI @Anonymous ,

You can add a calculate table with all unique product type, then use this table to link sale table and product information table.

Product =
DISTINCT (
    UNION (
        VALUES ( Sales[Product ID] ),
        VALUES ( 'Product Informations'[Product ID] )
    )
)

After these steps, you can create a table with product id(product), end date,  manufacturer(product information) fields and write a measure to summary sale to correspond manufacturer based on product id and end date.

Measure =
VAR currProduct =
    SELECTEDVALUE ( Product[Product ID] )
VAR currDate =
    MAX ( 'Product Information'[End date] )
VAR _dateList =
    CALCULATETABLE (
        VALUES ( 'Product Information'[End date] ),
        FILTER ( ALLSELECTED ( 'Product Information' ), [Product ID] = currProduct )
    )
VAR _start =
    MAXX ( FILTER ( _dateList, [End date] < currDate ), [End date] )
VAR _end =
    MINX ( FILTER ( _dateList, [End date] > currDate ), [End date] )
RETURN
    SUMX (
        FILTER (
            ALLSELECTED ( Sales ),
            [ProductID] = currProduct
                && [Sales Date] IN CALENDAR ( MIN ( _start, currDate ), MAX ( _end, currDate ) )
        ),
        [Sales]
    )

If above not help, please share some test data with same data structure for test and coding formula.(Notice: do mask on sensitive data)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft ,

Thanks so much for that! I'm going to give it a try and will let you know how it turns out.

Best regards
Bas