Show most recent record

Hi gancw1 ,

 

Here a suggestion:

 

 

And here the DAX:

MeasureHireResign = 
VAR _MostRecentHireDate   = CALCULATE ( MAX ( 'Table'[Joined Date] ), ALL ( 'Table' ) )
VAR _MostRecentResignDate = CALCULATE ( MAX ( 'Table'[Resigned Date] ), ALL ( 'Table' ) )
RETURN
IF ( 
    SELECTEDVALUE ( 'Table'[Joined Date]   ) = _MostRecentHireDate, "Last Hire",
    IF (
        SELECTEDVALUE ( 'Table'[Resigned Date] ) = _MostRecentResignDate, "Last Resignation", 
        BLANK()
    )
)

To get the exact same result that you want, I'd probably change the table structure a little bit (unpivot). 

 

I'll add another response for that.

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

 

 

 

Hi gancw1 ,

 

For the unpivoting, I usually use Power Query. I used the table from above and unpivoted on the two date columns. I also renamed the columns.

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRQ0lEyMtA31DcyMDICso0N9Q2NYJzgxJzUYqVYnWglJ5A6Q4gyYzDTFMb0CAKrcAarAIrCxEF838Si7NSSzLx0sBIXkJCxvjFMhbGBvgmMjarSVQFNWgHhmlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Joined Date" = _t, #"Resigned Date" = _t, Dept = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Joined Date", type date}, {"Resigned Date", type date}, {"Dept", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee", "Dept"}, "Type", "Date")
in
    #"Unpivoted Columns"

 

Now, we can use a similar DAX measure as per below:

 

Here the DAX measure:

MeasureHireResign2 = 
VAR _MostRecentHireDate   = CALCULATE ( MAX ( 'TablePivot'[Date] ), ALL ( 'TablePivot' ), TablePivot[Type] = "Joined Date" )
VAR _MostRecentResignDate = CALCULATE ( MAX ( 'TablePivot'[Date] ), ALL ( 'TablePivot' ), TablePivot[Type] = "Resigned Date" )
RETURN
IF ( 
    SELECTEDVALUE ( 'TablePivot'[Date] ) = _MostRecentHireDate && SELECTEDVALUE ( 'TablePivot'[Type] ) = "Joined Date", "Last Hire",
    IF (
        SELECTEDVALUE ( 'TablePivot'[Date] ) = _MostRecentResignDate && SELECTEDVALUE ( 'TablePivot'[Type] ) = "Resigned Date", "Last Resignation", 
        BLANK()
    )
)

 

Let me know if either of the two solutions work for you 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/