Historical Agent Reporting

[Jorgast]

Hello Power BI Friends

I am trying to work on historical agent performance based on their role at that time. Due to the nature of the data, I am unable to share exact information, but I can provide an example. I am sure there is a way to do this in Power BI but I am not sure how. can you help?

I have 4 tables:

• Date
• Agent Sales
• Current Employee Roster
• Historical employee roster

Joins are as follows

• Date [Date] -> Agent Sales [Date of Sale]
• Date [Date] -> Historical employee roster[Effective Date]
• Current Employee Roster -> Agent Sales [ID]
• Current Employee Roster -> Historical employee roster[ID Number]

What I want to be able to do:

Show the historical sales data which includes their position at that time, based on the date slicer.

CURRENT
Name	Position	Effective Date	ID Number
Agent 1	Service Agent 1	1/1/2021	123
Agent 1	Service Agent 2	6/6/2021	123
Agent 1	Sales Agent 1	9/9/2021	123
Agent 2	Service Agent 1	12/1/2020	234
Agent 2	Service Agent 2	5/1/2021	234
Agent 2	Service Agent 3	11/1/2021	234
Agent 3	Service Agent 2	2/1/2021	345
Agent 3	Sales Agent 1	8/1/2021	345
Agent 3	Sales Agent 2	10/1/2021	345

HISTORICAL
Name	Position	Effective Date	ID Number	is Current
Agent 1	Service Agent 1	1/1/2021	123	NO
Agent 1	Service Agent 2	6/6/2021	123	NO
Agent 1	Sales Agent 1	9/9/2021	123	YES
Agent 2	Service Agent 1	12/1/2020	234	NO
Agent 2	Service Agent 2	5/1/2021	234	NO
Agent 2	Service Agent 3	11/1/2021	234	YES
Agent 3	Service Agent 2	2/1/2021	345	NO
Agent 3	Sales Agent 1	8/1/2021	345	NO
Agent 3	Sales Agent 2	10/1/2021	345	YES

SALES
Name	ID Number	Date of Sale	Amount
Agent 1	123	9/1/2021	100
Agent 1	123	10/1/2021	200
Agent 1	123	11/1/2021	150
Agent 3	345	8/1/2021	400
Agent 3	345	9/1/2021	100
Agent 3	345	10/1/2021	200

SLICER	9/1/2021	10/1/2021
OUTPUT
Name	Position	Effective Date	ID Number	Sales
Agent 1	Sales Agent 1	9/9/2021	123	300
Agent 3	Sales Agent 1	8/1/2021	345	100
Agent 3	Sales Agent 2	10/1/2021	345	200

[v-luwang-msft]

Hi @Jorgast ，

What the relationship between 'Sale' [Date] and  HISTORICAL[Effective Date]?

Best Regards

Lucien

[Jorgast]

Currently, there is no relationship between the Sales table and the historical table