How to show latest status based on date

[POSPOS]

Hi All,

I have a table with three columns - Date, Emp, Status.

My requirement is to create a dax formula to show the latest status.

I have a sample data attached in this file 

Required output is :

Emp	Status
E1	Active
E2	Active
E3	Terminated

Based on the latest date, need to look for the latest status. Can some one pls suggest with a solution for this?

Thank you.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[v-kaiyue-msft]

Hi @POSPOS ,

Your solution is great, collinq  and PhilipTreacy . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference. 

you can use the following DAX formula in Power BI to get the latest status for each employee.

LatestStatus = 
VAR LatestDate = 
    CALCULATE(
        MAX('Sheet1'[Date]),
        ALLEXCEPT('Sheet1', 'Sheet1'[Emp])
    )
RETURN
    CALCULATE(
        VALUES('Sheet1'[Status]),
        'Sheet1'[Date] = LatestDate,
        ALLEXCEPT('Sheet1', 'Sheet1'[Emp])
    )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[collinq]

Hey @POSPOS ,

There are many ways to do this I am sure but I did it by creating a new table in DAX with this command:

MostRecentStatusTable =

ADDCOLUMNS(

    SUMMARIZE(

        'Sheet1',

        'Sheet1'[EMP],

        "MostRecentDate", MAX('Sheet1'[Date])

    ),

    "MostRecentStatus",

    VAR MostRecentDateForEMP = [MostRecentDate]

    RETURN

        CALCULATE(

            MAX('Sheet1'[Status]),

            'Sheet1'[Date] = MostRecentDateForEMP

        )

)

And, that gave me the result that you were looking for:

[PhilipTreacy · Accepted solution]

@POSPOS 

Download PBIX file with the example below

Try this, works for me

Latest Status = 

VAR _latest_date = CALCULATE(MAX('Sheet1'[Date]), FILTER(ALL('Sheet1'), 'Sheet1'[Emp] = SELECTEDVALUE(Sheet1[Emp])))

RETURN CALCULATE(MAX('Sheet1'[Status]), FILTER('Sheet1', 'Sheet1'[Emp] = SELECTEDVALUE(Sheet1[Emp]) && 'Sheet1'[Date] = _latest_date))

Regards

Phil