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