Need latest and second latest date with KEEP filters

[Anonymous]

I have a data in which I want to create a column which will categorize Date if its latest or second latest or not latest.
The latest and second latest will change according to the data available for specific product/region.

Geography	Industry	Date	Required Column
China	fuel	01 September 2022	Latest
China	fuel	01 August 2022	Second`
China	fuel	01 July 2022	Not latest
India	fuel	01 August 2022	Latest
India	fuel	01 July 2022	Second
India	fuel	01 June 2022	Not Latest

[RicoZhou · Accepted solution]

Hi @Anonymous ,

You can try this code to create a calculated column

Required Column =
VAR _RANK =
    RANKX (
        FILTER (
            'Table',
            'Table'[Geography] = EARLIER ( 'Table'[Geography] )
                && 'Table'[Industry] = EARLIER ( 'Table'[Industry] )
        ),
        'Table'[Date],
        ,
        DESC,
        DENSE
    )
RETURN
    SWITCH ( _RANK, 1, "Latest", 2, "Second", "Not Latest" )

Result is as below.

Best Regards,
Rico Zhou

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