I can’t work this out!
I have a classic Sales Table. Customer sales go up and down, so I’ve segmented my customers in Groups - High, Med, Low - and this changes every month. A customer can move from High to Med to Low and back again over a series of months.
I hold this in a table, Cust ID, Segment Class (Hi, Med, Low) and Month (the various Customers in each group that month). These are calculated outside of BI.
What I want to show is the various sales by Month for the total class so that I can see (eg) Total Customer Sales by Hi for each of the previous months. That will allow me to see if Hi customers are increasing / decreasing etc.
I can’t think how I would link this on a data model. I can link Class to Customer to Sales table, but this will only return Total Sales, as I can’t work the Date dimension into the model. I (think I) want All Customers in a Class in this month; total sales for that month for this customers. That creates too many date relationships in the model.