Alternative to Lookupvalue to use in virtual tables

[Anonymous]

Hi!

I need help creating a column inside a virtual table, so I cannot use a lookupvalue as a physical table is required...

This a dummy data to show what I need. This would be my Sales Table, where "Period" is not in date format, but text:

Here, in period 1, the Tshirt was classified as Shirt, but in the next period was reclassified as Clothes. This is making my future calculations look like they are 2 different products, while they are the same. I can create a summarize table without Category, but I need to be able to analyse that as well so I need to include it.

I could hard code a change, but each month some products get changed, and I cannot waste my time to look for them and change them. I'm trying now to add the column after the initial "ADDCOLUMNS( SUMMARIZE", but I cannot use lookupvalue to do it.

The result I'm looking for would be something like this, were if there is any discrepancy in the category, the one in Period 1 should be replaced with the category from Period 2 (the most recent one). 

This way they would be treated as just 1 product to make further analysis like Actual - Outlook.

This virtual table gets calculated according to the Periods that the user selects in the slicer, so I cannot create a table for this to change the category because depending on the periods, the result of the category might be different.

Is there a way I can create this column "Category", checking the product name and fetching the category from the Period 2?

Thank you very much!!

[AntrikshSharma · Accepted solution]

@Anonymous Try this:

Category Corrected = 
IF ( 
    ISINSCOPE ( 'Table'[Period] ),
    VAR MaxSelectedPeriod = 
        CALCULATE ( 
            MAX ( 'Table'[Period] ),
            ALLSELECTED ( 'Table' )
        )
    VAR MostRecentCategory = 
        CALCULATE ( 
            VALUES ( 'Table'[Category] ),
            'Table'[Period] = MaxSelectedPeriod,
            REMOVEFILTERS ( 'Table' )
        )
    RETURN MostRecentCategory
)

[Anonymous]

Hi @AntrikshSharma 

Can you share the pbix here to see it? I'm trying to adapt your code to add it to my virtual table but I'm having some issues. Maybe if I see it in the file I can adapt it better.

Million thanks for your help!!

[AntrikshSharma]

@Anonymous You can download from here: https://drive.google.com/file/d/1e90MuQ8b316hgoBS-n8l9kTuHGNJUD6B/view?usp=sharing

[Anonymous]

Hi @AntrikshSharma 

Thank you for replying!! The logic is that if the product gets reclassified, it always takes the classification of the latest period chosen.

If we have Period 3 as Shirt. Then if the user chooses Period 3 and 2 to compare, the Category should be Shirt, as it is the last one the one that remains.

If the user picks Period 3 and 1, it would be Shirt because it's the same category, but if Period 2 and 1 are chosen, then it would be Clothes, as Period 2 is the last one there.

I hope this makes what I need a bit more clear.

Thank you very much!!

[AntrikshSharma]

@Anonymous What happens if in Period 3 T-Shirt is again categorized as Shirt what should be the logic for that row?