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 )

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Samarth,

 

Thanks for your reply. It works excellently when I am doing it on the sample data I provided earlier however when I apply the same measures on real large dataset it produces some static dates. 

Would you please be able to run this on my cut down large dataset is in the link?

https://www.dropbox.com/s/w3v3bi4nvn9s8rr/Previous%20RMP%20Inspection%20%281%29.csv?dl=0

 

Appreciated your time!

 

Samarth_18's avatar
Samarth_18
Community 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.