Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Community
I am looking for some help trying to figure out how to determine the status of an employee based on a few criteria. In the sample screen shot below you can see an associate id, some dates and the employee status:
It is very easy to identify the employees where there is a single line and a termination date, or a single line and no termination date, however when employees transfer between cost centers, or relocate to another department the get 'Terminated' in the system (like the ones below. The ones below are also in the above screen shot but they have multiple lines. I have listed the Term Date and Status I want to see.
Associate ID | Actual Term | Status |
0007KM9IL | 9/21/2018 | Terminated |
01B8LTWDG | Active | |
02XJ0T14T | Actove |
I am trying to figure out a way to create a calculated column or a measure so that I am able to show to correct termination dates and statuses of employees.
Any help from you would be greatly appreciated!
Thanks
Ryan F
Hi @Anonymous ,
I have a method, but it's a bit cumbersome.
First, fill the null value of [Termination Date] with a date as small as possible in Edit Queries:
Second, Create two measures:
Actual Term =
VAR x =
CALCULATE(
MIN(Sheet3[Termination Date]),
ALLEXCEPT(
Sheet3,
Sheet3[Associate ID]
)
)
VAR y =
CALCULATE(
MAX(Sheet3[Termination Date]),
ALLEXCEPT(
Sheet3,
Sheet3[Associate ID]
)
)
RETURN
IF(
x = DATE(1100,1,1),
BLANK(),
y
)
Status =
IF(
[Actual Term] = BLANK(),
"Active",
MAX(Sheet3[Position Status])
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-lionel-msftsorry, prematurely posted.
The issue is that if an associate is actually terminated in June, all monthly reports prior to June will still show the status of the associate as terminated. Is there a work around for that?
@v-lionel-msftthis works to an extent. However the report is ran monthly and ALL data is appended to the end of the table. Is there an easy fix for that issue?
Hi @Anonymous ,
from your sample data above, what is your expected result?
Refer if this can help :
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |