Forum Discussion

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost Prodigy
2 years ago

Get latest value based on max date as calculated column

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   ...
v-weiyan1-msft's avatar
v-weiyan1-msft
Icon for Community Support rankCommunity Support
2 years ago

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.