Date filter

[103991]

Hello,

I'm having the following problem. I have a couple of sales tables relating to a date table as well as a persons table. 

Now, i'm adding a table to define the teams for each person. The problem i have is that people have been in different teams and can switch in the future, but everyone is doing the same activities. I have the startdate and enddate for each person for when they where in the same team. I need a date filter for the results of each team on an individual level, team level and department level (all teams). 

In an ideal world i would like it to connect to my date table, but i haven't been able to work that out. 

Example: Person x is working in a team A from the 1st of Januari - 1st of Februari. Then switches to team B from the 1st of Februari - 1st of March. When i set a date filter from the 1st of Janari - 1st of March and select team A. I only need the results of person X from the 1st of Januari - 1st of Februari.

Any suggestions?

[amitchandak · Accepted solution]

This is an SCD case. Please refer.

https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/

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

https://community.powerbi.com/t5/Desktop/Select-correct-slowly-changing-dimension-record-based-on/td...

https://community.powerbi.com/t5/Desktop/How-to-find-number-of-customers-in-a-SCD2-type-table/td-p/9...