Dynamic Total KPI By Supervisor

[ebp2022]

Hi, I work in an environment where change in employee supervisor is common and frequent. It could be the 3rd week of the month, the 2nd week, the first day, etc.

I have measures on multiple KPIs that need to be reported on manager > supervisor > employee hierarchy. My problem is I'm not sure how I could historically reflect the KPI of an employee to the right supervisor and manager depending on the date filter applied to the visual. 

I was thinking of grabbing the supervisor at a given date for the data tables but the 5 KPI data tables need to be aggregated to an overall performance score so I need the hierarchy to dynamically change. 

I hope you can point me to possible solutions to this as I've done hours and hours of search but maybe I'm not using the right keywords.

Any insights would be appreciated. Thank you!

[ebp2022]

My model is complex but this is enough to point me to the right direction. I honestly have not heard about this term "slowly changing dimension" but it's exactly what I needed. Thank you so much!

[DataInsights]

Glad to hear that!

[DataInsights · Accepted solution]

@ebp2022,

This sounds like a Type 2 Slowly Changing Dimension scenario. See the link below for a solution that you may be able to adapt. Would you be able to provide sample data (paste as table or upload to a file service like OneDrive), and screenshots of the data model and the expected result?

https://community.powerbi.com/t5/Desktop/Incorrect-Sum-of-Columns-and-Rows-in-Matrix/m-p/2470298#M88...