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 
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]
        )

 

 

2 Replies

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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)
     

     

  • 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.