Need Help - Calculating Sales when category is upgraded

Do you have a 'Category' table which defines which category a salesperson is in in each month?  Something like this:

If you don't, then I think this is what you need to be able to make progress.  I would have the [Month] column actually as a date (start of the month) as this will make it easier to join to your existing sales data table.  Specifically, I would join in Power Query as follows:

Add a [Start of Month] column to your sales data

From your sales data, merge on both Sales.[SalesPersonID] <-> Category.[SalesPersonID] and Sales.[Start of Month] <-> Category.[Month]

Expand & just keep [category]

From your 'Category' table, you'll also need to generate a 'Category Changes' table.  I would also do this in Power Query by:

Duplicating the 'Category' table

Renaming the [Category] column in this table to [Previous Category]

Adding a [Start of Previous Month] colmn

Joining this back to your catgory table on SalesPersonID <-> SalesPersonID and Month <-> [Start of Previous Month]

Filtering on  [Category] <> [Previous Category]

Happy to expand on any of the above if you need more detail.  I've just given an overview & am not sure how comfortable you are in Power Query already.

Hi,

Please find attached the solution file.

Hope this helps.

Thank you for your help, it was very sweet of you to do so.  Highly appreciate.

Thank you