Show most recent record

[gancw1]

I have a list of employees with the joined and resigned date.

Employee	Joined Date	Resigned Date	Dept
A	20/1/2022	31/12/2022	Sales
B	1/1/2023	1/5/2023	HR
C	15/2/2023		Marketing
D	3/3/2023	30/4/2023	Marketing
E	30/4/2023		Sales

How can I show the most recent hire (E) and the most recent resignation (B)?

Employee	Category	Date	Dept
E	Last Hire	30/4/2023	Sales
B	Last resignation	1/5/2023	HR

Thanks

[tackytechtom · Accepted solution]

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/

[tackytechtom · Accepted solution]

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/

[tackytechtom · Accepted solution]

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/

[gancw1]

What if I want to show the last 3 most recent hires. How do I do that ?

Employee	Joined Date	Dept
E	30/4/2023	Sales
D	3/3/2023	Marketing
C	15/2/2023	Marketing

[tackytechtom]

Hi @gancw1 ,

You can solve such things with measures, too, but I usually recommend using the Filter pane as you most likely want to keep down the number of measures in your model:

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

[gancw1]

noted and thanks

[gancw1]

Thanks for the quick response !