Parent-Child Hierarchies with Date Dependencies

[Anonymous]

I am looking for a way to model a parent child relationship in which the parent is date dependant. For example, if we have an employee that switches teams I need to be able to represent which manager the employee reported to based on the date context.

employeeHierachy:

EmployeeId      ParentId      StartDate      EndDate

A                                          1/1/2016   

D                                          1/1/2019 

B                       A                 1/2/2017      2/1/2019

C                       D                 1/1/2016

B                       D                  2/2/2019      

factSales:

Deal     Employee     Amount    CloseDate

1          B                   500           2/1/2018

2          B                   150           6/20/2018

3          B                   400           5/1/2019

4          B                   200           7/30/2019

The goal is to be able to write a measure to attribute amounts at the top level of the heirarchy as follows

Lifetime Total Sales Amount:

EmployeeId     [TotalSales]

A                      650

D                      600

But when I try to create the calculated column "Path" in the employeeHeirarchy table as follows I receive the error below

Path = PATH(employeeHierachy[EmployeeId], employeeHierachy[ParentId])

Each value in 'employeeHierachy'[EmployeeId] must have the same value in employeeHierachy[ParentId]. The value 'B' has multiple values.

Does anyone know of a workaround for this?

[Anonymous · Accepted solution]

For each and every date you have to replicate the whole table of dependencies. Once you have it, you connect your Date table to the date field in the expanded table and this is how you obtain a different table of dependencies for each and every date.

Best
Darek