Finding two most recent dates from one table

[Anonymous]

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:

Assetid	Inspection Date
B000248	14/09/2020
B000248	19/09/2019
B000248	10/09/2018

Sample Output:

Assetid	Most Recent Date_1	Most Recent Date_2	Date Difference(Days)
B000248	14/09/2020	19/09/2019	361

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

Thanks

[Anonymous]

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 · Accepted solution]

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]

Thanks! Works Perfectly. Thanks again.

[Samarth_18]

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