Forum Discussion
Calculating duration between two dates in different rows based on duplicate value
- 6 years ago
This calculated column will do the trick:
Date Difference = VAR CurrentAccount = [Account_ID] VAR CurrentRecordSet = FILTER( 'DateDiff Table', 'DateDiff Table'[Account_ID] = CurrentAccount ) VAR AccountCount = COUNTX( CurrentRecordSet, 'DateDiff Table'[Account_ID] ) VAR CreatedDate = IF( AccountCount > 1, MAXX( CurrentRecordSet, 'DateDiff Table'[Created Date] ), 0 ) VAR SentDate = IF( AccountCount > 1, MINX( CurrentRecordSet, 'DateDiff Table'[Sent Date] ), 0 ) VAR DateDifference = DATEDIFF( SentDate, CreatedDate, DAY ) RETURN DateDifferenceIt returns zero if there is only one record for an account.
If you want it to return the dates between sent and created even if there is only one record, get rid of the IF() function. So for example:
VAR SentDate = IF( AccountCount > 1, MINX( CurrentRecordSet, 'DateDiff Table'[Sent Date] ), 0 )becomes
VAR SentDate = MINX( CurrentRecordSet, 'DateDiff Table'[Sent Date] )Same logic for the CreatedDate variable. You didn't specify in your OP, so wasn't sure how you wanted those handled.
Hi edhans ! Thank you for your reply, your column has exactly what I am looking for. I'm not sure why I am getting the 'Token Eof Expected' error where the CurrentAccount expression is. Any thoughts?
VAR CurrentAccount = [Account_ID]
Here are the data types that I am using:
Case_ID = Text
Account_ID = Text
Sent Date = Date
Report Date = Date
Thank you again!
Jenny
Anonymous I'd need to see a screenshot of the error. If you are entereing this as a calculated column, it should work. THat syntax will not work as a measure.