Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Calculations with multiple dates

Hi everyone, 


I am working on an HR measure where we are trying to see how often leaders (positions) are changing in our organization. 

I have for each position, the employee who held the position, and the assignment start and end date. 

What I am trying to achieve is to count how many employees held a position in a year and assign a status (2 or less, perfect, 3 ok, more than 3 bad) and then calculate the % per department per year of how many positions are in each status. like this: 

status by dept by year.png
















Here is the link to the sample data: 

I can get the count to work by year by creating a date table with two inactive relationships, but to assign a status I created a sub-table from my main one, but  the date or year filter don't work on that table since the relationship is in active. 


Any help on this is appreciated. I might be going at it all wrong 😅since it's my first time working with data with multiple dates. 




Frequent Visitor

Hi @wdx223_Daniel @


Thank you for your reply. I tried it and for some reason I get the same results repeated for each year but it's different by the department. 

Do I need to create a relationship with the DimStatus table and any other tables? 




DimStatus has not any relationship with any table.

have you put the column of Status on the legend?

Super User
Super User

Firstly,create a new table



Secondly,create a measure



Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors