Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @Fazal
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 )
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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!
Hi @Fazal
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 )
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Thanks! Works Perfectly. Thanks again.
Hi @Fazal
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |