Slowly changing dimension

[kpkexcel]

Hi all

I have a challenge with a report, where I would like to report on two simple dimensions: Product and Chain. Fact table contains a turnover per week per product per chain.

Things is I would like to report on a third dimension that changes over time. In this case it's a color on the product that changes from week to week.

I would like the following:
1) to be able to report turnover where I am able to slice on chain, product, week and color.
2) to be able to report turnover where I filter on only those product that are comparable from week to week based on their color.

I have uploaded an excel file here with an example:
https://drive.google.com/file/d/0B9lHVE3nc8PiaVJTMWp1R1JidjQ/view?usp=sharing

Looking forward to hear your suggestions.

 

[Matt Allington]

Your Product Colour table is simply another data table. You should create a product colour lookup table that contains all the colours and use that to filter the colour table, but this will not filter the other fact table. If you are using Excel 2016 or Power BI desktop, you can turn on bidirectional filtering and that should solve the problem. However if you have Excel 2010/13 you will need to use the many to many pattern to force the filtering you want.

I have posted a working sample workbook containing the many to many pattern here https://dl.dropboxusercontent.com/u/30711565/pp example.xlsx

 

[kpkexcel]

Hi Matt

Thanks a lot. That really helped me with my question no. 1.

Maybe it's easy for you to answer on my second question:

I would like to have a additional measure that return Turnover only if product has the same color for selected week + the week before.

e.g.:
Product = Cola, Chain = Brugsen, Color = Green in both week 1 and 2.
Here Turnover for week 2 can be presented.

another case:
Product = Sprite, Chain = Fakta, Color = Green in only week 2.
Here Turnover can not be presented.

I guess it's a filter that need to added to measure. I am really curious to see a solution on that.

 

[Matt Allington]

Well that is quite tricky. This works on the test data

Total Sales Color 2 Consec Periods=IF (
    CALCULATE (
        IF ( HASONEVALUE ( Colours[Colours] ), VALUES ( Colours[Colours] ) ),
        ProductColour
    )
        = CALCULATE (
            CALCULATE (
                IF ( HASONEVALUE ( Colours[Colours] ), VALUES ( Colours[Colours] ) ),
                ProductColour
            ),
            FILTER ( ALL ( Calendar ), Calendar[Week] = MAX ( Calendar[Week] ) - 1 )
        ),
    [Total Turnover Colour Filtered]
)

But it will only work by checking back 1 week (ie not 2 weeks, not checking forward etc). Also you should build a proper calendar table with an ID file to do the time shifts (ie to check back on the previous week). Read my blog on that here Power Pivot Calendar Tables -

I have updated the sample file I posted earlier https://dl.dropboxusercontent.com/u/30711565/pp example 2.xlsx