Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating duration between two dates in different rows based on duplicate value

Hi, I want to calculate the duration (# of days) between two fields based on the same value.   For example, I have data:   Case_ID Created Date Sent Date Account_ID Duplicates 303 3/1/...
  • edhans's avatar
    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
        DateDifference
    

     

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