Forum Discussion
Historical Agent Reporting
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 |
2 Replies
Hi Jorgast ，
What the relationship between 'Sale' [Date] and HISTORICAL[Effective Date]?
Best Regards
Lucien
Currently, there is no relationship between the Sales table and the historical table
