Forum Discussion

luckygirl's avatar
luckygirl
Helper I
1 year ago
Solved

Sort a table before calculate based on date

I have a table like below. Code CompletedDate ProductDetail 1 1/01/2018 A 1 1/04/2019 B 1 23/05/2021 C 2 9/02/2018 D 2 5/05/2021 C 2 6/05/2022 A 3 1/01/201...
  • Irwan's avatar
    Irwan
    1 year ago

    hello luckygirl 

     

    please check if this accomodate your need.

    create a new calculated column with following DAX

    Target = 
    var _Min = 
    MINX(
        FILTER(
            'Table',
            'Table'[CompletedDate]>EARLIER('Table'[CompletedDate])&&
            'Table'[Code]=EARLIER('Table'[Code])
        ),
        'Table'[CompletedDate]
    )
    Return
    MINX(
        FILTER(
            'Table',
            'Table'[CompletedDate]=_Min
        ),
        'Table'[ProductDetail]
    )
     
    Hope this will help.
    Thank you.
Irwan's avatar
Irwan
Super User
1 year ago

hello luckygirl 

 

please check if this accomodate your need.

create a new calculated column with following DAX

Target = 
var _Min = 
MINX(
    FILTER(
        'Table',
        'Table'[CompletedDate]>EARLIER('Table'[CompletedDate])&&
        'Table'[Code]=EARLIER('Table'[Code])
    ),
    'Table'[CompletedDate]
)
Return
MINX(
    FILTER(
        'Table',
        'Table'[CompletedDate]=_Min
    ),
    'Table'[ProductDetail]
)
 
Hope this will help.
Thank you.