Forum Discussion

mangchaaBI's avatar
mangchaaBI
Helper II
3 years ago
Solved

Days Difference Between 2 Data

Hi everyone,

 

Looking for guidance/assistance again 🙂

 

Not sure if I can explain this properly but I would want to transform this table with a unique EntityCode, their Notelogs (NoteCode which are also unique, and the Date when the Notes were created into something that will get the average number of days in between their calls 

 

Desired output is at the bottom where in it summarizes the average days per EntityCode

 

 

Thank you in Advance!

Need Help
Tutorial Requests
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi mangchaaBI 

    To achieve your goal you need at the first step to add a calculated column for days diff :

    DaysBetweenNextRow =
    VAR CurrentEntity = 'table'[EntityCode]
    VAR CurrentDate = 'table'[DateCreated]
    VAR NextDate =
        CALCULATE(
            MIN('table'[DateCreated]),
            FILTER(
                'table',
                'table'[EntityCode] = CurrentEntity &&
                'table'[DateCreated] > CurrentDate
            )
        )
    RETURN
        IF(ISBLANK(NextDate), BLANK(), DATEDIFF(CurrentDate, NextDate, DAY))

    2. Add measures for average and count:

    averaDays = averagex(values('Table'[EntityCode]),AVERAGE('Table'[DaysBetweenNextRow]))
    count calls = DISTINCTCOUNT('Table'[NoteCode])

    Link to a sample file 

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

1 Reply

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 years ago

    Hi mangchaaBI 

    To achieve your goal you need at the first step to add a calculated column for days diff :

    DaysBetweenNextRow =
    VAR CurrentEntity = 'table'[EntityCode]
    VAR CurrentDate = 'table'[DateCreated]
    VAR NextDate =
        CALCULATE(
            MIN('table'[DateCreated]),
            FILTER(
                'table',
                'table'[EntityCode] = CurrentEntity &&
                'table'[DateCreated] > CurrentDate
            )
        )
    RETURN
        IF(ISBLANK(NextDate), BLANK(), DATEDIFF(CurrentDate, NextDate, DAY))

    2. Add measures for average and count:

    averaDays = averagex(values('Table'[EntityCode]),AVERAGE('Table'[DaysBetweenNextRow]))
    count calls = DISTINCTCOUNT('Table'[NoteCode])

    Link to a sample file 

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly