Count transactions based on multiple variables from another table, including date ranges

[WavePkjnm242]

Good afternoon kind Power BI users,

Here's a simplified version of my challenge:

I have a table of transactions and a table of salespeople and their team affiliations. People move from team to team frequently, and I have the start and end dates for each team affiliation.

In the Transactions table, the team that made the sale has not been recorded, just the individual SalespersonID. (The AttributeToTeam column does not actually exist.)

I need to count sales per team for the last 4 years. How can I do this? I'd like to just add a "Team" column to the transactions table, but I would need to create that based on the SalespersonID and SaleDate—in conjunction with the TeamDateFrom and the TeamDateTo in the EmployeeTeamDates table.

In other words, the AttributeToTeam column in the Transactions table is what I don't know how to build, and maybe a column like that is not the best solution. It could be a new table, anything...

Also note, the null values in the TeamDateTo column indicate that the person is a current employee.

Any thoughts? Here's a pbix:

TransactionsByTeam_pbix 

Thank you!

Nick

[WavePkjnm242]

I am schooled and humbled. Thank you!

[amitchandak · Accepted solution]

@WavePkjnm242 , This SCD case, refer

SCD
Guyinacube - https://www.youtube.com/watch?v=tKeaQpWynzg

https://www.youtube.com/watch?v=E1ZABKBpkdg

https://www.zartis.com/scd-implementation-with-temporal-tables-in-power-bi/

regression like scatter visual