topic Re: Calculations with multiple dates in DAX Commands and Tips

Calculations with multiple dates

Omneah — Wed, 30 Jun 2021 05:57:25 GMT

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:

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

Re: Calculations with multiple dates

wdx223_Daniel — Thu, 01 Jul 2021 03:31:28 GMT

Firstly,create a new table

Secondly,create a measure

Re: Calculations with multiple dates

Omneah — Fri, 02 Jul 2021 11:48:48 GMT

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

Re: Calculations with multiple dates

wdx223_Daniel — Mon, 05 Jul 2021 00:15:45 GMT

DimStatus has not any relationship with any table.

have you put the column of Status on the legend?