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

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