Days Difference Between 2 Data

[mangchaaBI]

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!

[Ritaf1983 · Accepted solution]

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