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.