Best way to group salespeople by team by month

[EnviroJack]

Hi all

I have a report where I can split income by sales person by month. I now want to group them into teams (channel, region etc). the complication is some of them have moved teams so if I put them in the current groupings the historic data is wrong.

I can imagine a table which allocates them to teams by month but I'm not quite sure how to do this?

Has anyone done this or have a good idea how to do it?

Thanks

Jack

[v-piga-msft]

Hi @EnviroJack ,

Normally, we could create new group by clicking the column and selecting New Group to group the column directly.

However, I still a little confused about your scenario.

If it is convenient, could you share some data sample and your desired output so that we could help further on it?

Best  Regards,

Cherry

[EnviroJack]

Hi Cherry

Thanks for responding. For example I want to split sales totals for Team A vs Team B

Sales values are recorded by Sales Person

Team A consists of Sales People: Bob, Fred and Julie, Mark

Team B consists of Sales People:  Sarah, John and Kate

However, Kate only moved into Team B in  January 2019, before this she was in Team A

Is there a way of reflected which team each member belongs to in each month so that Team total is correct when reporting across periods? 

The Sales data is saved in a table like the below:

Table 1
Sale no.	Date	Amount	Sales Person
1	01/12/2018	10	Bob
2	01/12/2018	20	Kate
3	12/12/2018	10	Sarah
4	01/01/2019	5	Kate
5	01/01/2019	15	Sarah

I would like the report to show period totals by team:

	Jan-18	Feb-18	Dec-18	Jan-19
Team A			20	0
Team B			10	20

So imagine some sort of dimension table of team member by month? But not sure how this would work.

Sales Person	Team Dec 2018	Team Jan 2019
Bob	A	A
Fred	A	A
Julie	A	A
Mark	A	A
Sarah	B	B
John	B	B
Kate	A	B

Thanks

Jack