How to maintain customer category sales data when they change segments/silos

[darliusk]

Hello

Although this may already be covered somewhere, I could not find it on my searches.

I have a sales dashboard which allocates customers into different segments ("silos") based on anticipated revenue sales.

Over the course of time, some customers may be moved into a different segment (Platinum, Distributor, Wholesale, Retail) if their sales performance changes.

Currently, I have set up my customer dataset to include segment information (Customer code, Customer Name, Silo(Segment), State, Country, Region), however, if I were to change the segment variable on this dataset, it will also change the historical sales data, moving it over to the new segment.

How do I retain a customers historical sales information in their old segment up to the date they change in the new segment?

I gather I will need to split out Segment into a new dataset (with Customer code as the Primary Key), and then use an IF statement in DAX (with state and end dates), but am not sure how to do it.

Please help me find the most simple and elegant way to do this.

If you think there is a better way, I am open to suggestions, so long as it does not mean a massive change to my schema.

Regards

Darliusk

[darliusk]

 Thanks @TomasAndersson , this looks like a solid foundation to my question. Appreciate the assistance!

@darliusk 

[TomasAndersson · Accepted solution]

Hi!
Yes, some variation of what you're suggesting is probably the easiest solution.

I made a simple model and a measure to try it out. You can see if can give you a few ideas of how to solve your problem.

Two simple tables, a dimension Customer table and a fact Silo table:

Notice that Al has two entries in Silo with different date, one in Jan and one in Feb.

Then a simple model with these two + a date table: 

And a measure to find Silo based on date:

Current Silo = 
if(
    ISINSCOPE(Customer[Customer]),
        CALCULATE(LOOKUPVALUE(Silo[Silo],Silo[Date],LASTDATE(Silo[Date])),
            FILTER(ALL(Dates[Date]), Dates[Date] <= max(Dates[Date])
            )
        )
)

Result:

With that you should be able to do calculations that depend on what silo a customer belongs to at a given date. Not sure if it covers all you need, but hopefully a start.

Good luck!