Skip to main content
cancel
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

Reply
Omneah
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: https://drive.google.com/file/d/1tOY_X2VUg6RMpSPljmdebQU8L4Vl8UC0/view?usp=sharing 

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. 

 

Thanks

 

3 REPLIES 3
Omneah
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? 

 

Thanks,

Omneah

DimStatus has not any relationship with any table.

have you put the column of Status on the legend?

wdx223_Daniel
Super User
Super User

Firstly,create a new table

wdx223_Daniel_0-1625110195982.png

 

Secondly,create a measure

wdx223_Daniel_1-1625110272731.png

 

Helpful resources

Announcements
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