Forum Discussion
Resolver IINeed 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:
SalesPersonID Category Month 1 Silver Apr-2023 2 Silver Apr-2023 3 Silver Apr-2023 4 Silver Apr-2023 1 Gold May-2023 2 Gold May-2023 3 Gold May-2023 4 Silver May-2023 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
Resolver IYes, generating a 'Category Changes' table, as outlined in my previous response, would solve both your problems.
Resolver IISorry to bother you i am not getting this part. In my sales table category is not there so how do i merge this. My Query marked in bold.. It it is taking your time, you may ignore the question. Thanks for your assistance
Your reply:
Add a [Start of Month] column to your sales data
Month Column is there in the Sales data
From your sales data, merge on both Sales.[SalesPersonID] <-> Category.[SalesPersonID] and Sales.[Start of Month] <-> Category.[Month]
Expand & just keep [category]
I have to merge
Sales.[SalesPersonID] <-> Category.[SalesPersonID
In my Sales table i have the sales id, but i don thave category in it, How to i get the category in this, ie from category table for that respective date how to i plot the respective month 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]
2) and Sales.[Start of Month] <-> Category.[Month]
In the sales table i have the month but not the category