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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Pull Max Dates or Blanks based on Criteria

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:

Snapshot.PNG

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 IDActual TermStatus
0007KM9IL  9/21/2018Terminated
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

5 REPLIES 5
v-lionel-msft
Community Support
Community Support

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:

d6.PNGd7.PNG

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])
) 

 

d8.PNG

 

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.

Anonymous
Not applicable

@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?

Anonymous
Not applicable

@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?

mussaenda
Super User
Super User

Hi @Anonymous ,

 

from your sample data above, what is your expected result?

amitchandak
Super User
Super User

Refer if this can help :

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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