calc column: Trying to calculate MAX date by group with NULL being MAX over other dates

[OneWithQuestion]

I can get a MAX value of a group, and that works fine.  However, my complication is I need BLANK/NULL values to win out over any other date.

I toyed with replacing them with a far future date so MAX would always return that date (the year 9999)

I also tried just doing the conversion inside my calc and returning BLANK in the field ... either way is fine really.

However, when I try to do my calculations I can either get it to work properly, except that BLANK is always returned as the lowest value OR when I try to swap out blank with the year 9999 it breaks and it does not return the MAX value for all rows of the given group:

UniquePersonIdentifier is the ID for an actual person (like SSN if SSN was actually unique).

EmployeeID is the ID for that position, but if I work for the company, then leave, then come back a few years later...I get a new EmployeeID (say seasonal employees especially good example).

What I need to find is the MAX or Most Recent TermDt by unique person.  If they are still employed then I want BLANK (or 9999) but if they no longer work for us, I just want the MAX TermDt out of all their employment records (if they held five different jobs over the years I want the MAX of all 5).

Desired output:

Note the color coded rows are for the same person (actual data is not sorted, just sorted to make it easier to read).

How can I accomplish either Desired Calc 1 or 2 with a calculated column?

I tried =CALCULATE(MAX(EmplTermDt),FILTER(UniquePersonIdentifier = EARLIER(UniquePersonIdentifier))

and that worked, except that when it hit a BLANK value it did NOT consider that a MAX value so for unique ID 3 I would get 2016/08/01 in the output instead of the BLANK.

I tried doing an IF ISBLANK swap, but that breaks the MAX statement.

I tried doing a summarize with a filter to do the earlier.... and using the filter as the source table...also no good 😞

Thanks!

SAMPLE DATA AS CSV:

UniquePersonIdentifier,EmployeeID,EmplTermDt
3,a7x,2014/05/23
3,96z,2016/08/01
3,z83,
8,b92,
12,1c9,2017/01/01
12,kh1,2014/05/20
15,m72,2017/10/15
75,c91,

[nickchobotar]

Hi @OneWithQuestion

Is this something are you looking for ?

MAX = 
IF (
    Table1[EmplTermDt],
    CALCULATE (
        MAX ( Table1[EmplTermDt] ),
        ALLEXCEPT ( Table1, Table1[UniquePersonIdentifier] )
    ),
    BLANK ()
)

[OneWithQuestion]

No, that was the issue I was running into 🙂

If the field is NULL (Blank) then I want THAT to be the max.

If blank then they are NOT termed and are current employees.

Granted I could make a NEW calc column that just swaps BLANK out for 9999-01-01 or something, but I really wanted to do all the work in a single calc instead of loading multiple columns into the data model.

So if a UniquePersonID has 3 values (like number 3) and one of them is NULL/BLANK then it means that person is still employed and I want my calc column to show NULL/BLANK for all 3 rows (or 9999 if we swap it that way)

[nickchobotar · Accepted solution]

@OneWithQuestion

You have an interesting requirement.  Please try this one and let me know if it works for you .

MAX = 
IF(
    CONTAINS(
        Table1,
        Table1[UniquePersonIdentifier],Table1[UniquePersonIdentifier],
        Table1[EmplTermDt], BLANK()
    ), 0, // Use number 2 instead of 0 to return 1/1/1900 instead of 12/30/1899
    CALCULATE(
        MAX(Table1[EmplTermDt]), 
        ALLEXCEPT(Table1, Table1[UniquePersonIdentifier])
    )
)

 

N -

[OneWithQuestion]

That did it, thanks!

I had NOT used CONTAINS before, that was an interesting and informative approach, thanks!

[nickchobotar]

@OneWithQuestion    You are welcome. I am glad It worked out for you.

N -