How to calculate minimum date based security column.

[Bansi008]

Hi there,

Can someone please help me here to calculate the minimum date in a separate column based on the security column. 

sample input data:- 

SECURITY	DATE
Amazone	6/30/2024
Flipkart	6/30/2024
Myntra	6/30/2024
Ajio	6/30/2024
Alibaba	6/30/2024
Flipkart	3/31/2024
Amazone	3/31/2024
Myntra	3/31/2024
Alibaba	3/31/2024
Ajio	3/31/2024

Expected output:- 

SECURITY	DATE	MIN_DATE
Amazone	6/30/2024	3/31/2024
Flipkart	6/30/2024	3/31/2024
Myntra	6/30/2024	3/31/2024
Ajio	6/30/2024	3/31/2024
Alibaba	6/30/2024	3/31/2024
Flipkart	3/31/2024	3/31/2024
Amazone	3/31/2024	3/31/2024
Myntra	3/31/2024	3/31/2024
Alibaba	3/31/2024	3/31/2024
Ajio	3/31/2024	3/31/2024

[Irwan · Accepted solution]

hello @Bansi008 

please check if this accomodate your need.

MIN_DATE =
MINX(
    FILTER(
        'Table',
        'Table'[SECURITY]='Table'[SECURITY]&&
        'Table'[DATE]<=EARLIER('Table'[DATE])
    ),
    'Table'[DATE]
)

Hope this will help you.

Thank you

[Bansi008]

@Irwan thank you this is helpful

[Irwan]

Hello @Bansi008 

Glad to be a help.

Thank you.