Report salesrep hierarchy depending on dates

[pmargari]

Hello all,

Any tips tricks how I could manage a salesrep hierarchy that will change over time and I need to report depending on the structure that each date will contain at the time of the transaction :
In the below example some salesrep's from 2022 to 2023 have change their team leader , what I want to achieve will be if I report from 01-01-2002 till 31-12-2022 Alfred belongs to team leader = Guardiola, but if I report Alfred in 2023 will belong to team leader = Klopp 

How could I preform this relationship between this salesrep structure / Calendar Dates / Transaction data 

Appreciate your inputs 
KR

[bolfri]

I am sorry, but I don't understand the issue here. Isn't that the table that you will be using in Power BI to report Alfred or it's desire output (then how the imput looks like?)?

[pmargari]

hello bolfri 

thanks for your reply !

lets considered this table the master table of the sales team structure.

What I will need to report / connect is the sales transactions table that depending on the sales row date Alfred will belong to one or the other team member , hope this explain better my need. 
thanks 

[bolfri]

Since you haven't provided any sample data, let's talk purely theoretically.

You have a sales table (fact_sales), which has information about a sales event + added information about the seller in the form of some ID.

event, qty, event_date, seller_id

You have a table of assigning sellers to leaders (fact_seller), which contains information about when the seller worked for which leader.

date_start, date_end, seller_id, leader

Due to the fact that the seller could work for several different leaders at different times, we have two scenarios to consider:
1) we report the seller's sales according to the last leader for which the person worked (then we have a clear situation that a given seller has one last leader) and all historical sales of this seller are credited to this leader. The disadvantage of this solution is the fact that we do not have information about whether the seller decreased or increased his sales after the leader change, but the structure is nice on slicers, because one seller does not belong to many leaders.
2) we report the seller's sales according to the leader the seller worked for at the time of the event, so each leader has a history of sales made by his sellers when they worked for him. The disadvantage of this solution is the fact that the seller appears in the filters of many leaders and if we present data in the pair: leader & seller, we do not have the continuity of his sales.

Based on my experience in working with this type of situation, I build a solution that allows me to observe sales from two perspectives.

1) In Power Query M, I add information from fact_seller to the fact_sales table, which assigns a leader based on the fact that the event occurred between the start date and the end date of the leader assignment, and information about the seller.

fact_sales looks like this:
event, qty, event_date, seller_id, event_leader_id

2) In Power Query M I build a table with current employment status (current structure) and call it dim_seller (where seller id is the key).
seller_id, last_leader_id

3) In Power Query M I build a table with dim_leader
leader_id

4) In Power BI I build relationships:
fact_sales to dim_seller on seller_id = seller_id (active relationship)
dim_seller to dim_leader on last_leader_id = leader_id (active relationship)
fact_sales to dim_leader on event_leader_id = leader_id (inactive relationship)

5) In Power BI I create a measure:
Sales = SUM(Qty)

Sales by leader = CALCULATE([Sales], USERELATIONSHIP( fact_sales to dim_leader formula...))

In this case you can recive both scenario depending on the viz and business logic you need. 🙂

[pmargari]

hello bolfri
Thanks for your valued feedback and I'm ging to try as you describe as it sounds quite straightforward !
Thanks, and will come back