Get latest value based on max date as calculated column

[olimilo]

Example data below:

ID		Fty		Status		Date
1		Facility A		Active		1/1/2020
2		Facility B		Active		1/1/2021
3		Facility A		Active		1/1/2021
4		Facility C		Active		1/1/2022
5		Facility A		Inactive		1/1/2022
6		Facility B		Active		1/1/2023

I'm trying to get the Status of the Facility based on the Date column. I am able to do this as a measure below however I would like to get it as a calculated column?

Expected output:

Fty		Status
Facility A		Inactive
Facility B		Active
Facility C		Active

~Current Status = 
    VAR _val = MAX('Data'[Date])
    RETURN
        MAXX(
            FILTER(
                'Data',
                'Data'[Date] = _val && 'Data'[Fty] = MAX('Data'[Fty])
            ),
            'Data'[Status]
        )

[v-weiyan1-msft]

Hi @olimilo ,

@talespin nice method！And you can also consider using the following code to meet your needs.
Based on your description, please try code as below to create a Calcualted table.

New Table =
VAR MaxDate =
    MAXX ( ALLEXCEPT ( 'Table', 'Table'[Fty] ), 'Table'[Date] )
VAR TempTable =
    ADDCOLUMNS (
        VALUES ( 'Table'[Fty] ),
        "Status",
            MAXX (
                FILTER (
                    'Table',
                    'Table'[Fty] = EARLIER ( 'Table'[Fty] )
                        && 'Table'[Date] = [MaxDate]
                ),
                'Table'[Status]
            )
    )
RETURN
    TempTable

Result is as below.

Best Regards,
Yulia Yan

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

[talespin]

hi @olimilo ,

Please test this.

First calculating max date for each facility by removing all filters on table except facility.

Then using this date to find the status for each facility.

CurrentStatus =

VAR _fty = TestTable7[Fty]

VAR _maxDateFty = CALCULATE(MAX(TestTable7[Date]), REMOVEFILTERS(TestTable7), TestTable7[Fty] = _fty)

return CALCULATE(MAX(TestTable7[Status]), REMOVEFILTERS(TestTable7), TestTable7[Fty] = _fty && TestTable7[Date] = _maxDateFty)