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 those 2 dates.

 

Sample Data:

AssetidInspection Date
B00024814/09/2020
B000248

 19/09/2019

B000248

10/09/2018

 

Sample Output:

 

AssetidMost Recent Date_1Most Recent Date_2Date Difference(Days)
B00024814/09/202019/09/2019361

 

Hope it gives an idea of what I am trying to achieve. Really appreciate any help.

 

Thanks

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 )

     

4 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    5 years ago

    Hi Anonymous 

    You can create measure with below code:-

    1. For max date:-

    max_date = CALCULATE(MAX(Max_dates[Inspection Date]),ALL(Max_dates))

    2. For second recent date- 

    second_max_date = CALCULATE(MAX(Max_dates[Inspection Date]),FILTER(ALL(Max_dates),Max_dates[Inspection Date] < [max_date]))

    3. Date diff

    date_diff = DATEDIFF([second_max_date],[max_date],DAY)

    Output:-

    Thanks,

    Samarth

    • 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.