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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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