Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Finding two most recent dates from one table

Hi there,   Need urgent assistance regarding date calculation. I have table where there are multiple dates per assetid. I would like to extract most two recent dates and difference in days between ...
Need Help
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    Hi Anonymous 

     

    You can update your code as below

     

    _max_date =
CALCULATE (
    MAX ( 'Previous RMP Inspection (1)'[Date Inspected] ),
    FILTER (
        ALL ( 'Previous RMP Inspection (1)' ),
        'Previous RMP Inspection (1)'[Asset Id]
            = MAX ( 'Previous RMP Inspection (1)'[Asset Id] )
    )
)
    _second_max_date =
CALCULATE (
    MAX ( 'Previous RMP Inspection (1)'[Date Inspected] ),
    FILTER (
        ALL ( 'Previous RMP Inspection (1)' ),
        'Previous RMP Inspection (1)'[Date Inspected] < [_max_date]
            && 'Previous RMP Inspection (1)'[Asset Id]
                = MAX ( 'Previous RMP Inspection (1)'[Asset Id] )
    )
)
    _date_diff =
DATEDIFF ( [_second_max_date], [_max_date], DAY )

     

Samarth_18's avatar
Samarth_18
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Anonymous 

 

You can update your code as below

 

_max_date =
CALCULATE (
    MAX ( 'Previous RMP Inspection (1)'[Date Inspected] ),
    FILTER (
        ALL ( 'Previous RMP Inspection (1)' ),
        'Previous RMP Inspection (1)'[Asset Id]
            = MAX ( 'Previous RMP Inspection (1)'[Asset Id] )
    )
)
_second_max_date =
CALCULATE (
    MAX ( 'Previous RMP Inspection (1)'[Date Inspected] ),
    FILTER (
        ALL ( 'Previous RMP Inspection (1)' ),
        'Previous RMP Inspection (1)'[Date Inspected] < [_max_date]
            && 'Previous RMP Inspection (1)'[Asset Id]
                = MAX ( 'Previous RMP Inspection (1)'[Asset Id] )
    )
)
_date_diff =
DATEDIFF ( [_second_max_date], [_max_date], DAY )

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks! Works Perfectly. Thanks again.