Calculation Duration with IF statement

[ToddMate]

Hi All,

Can you please help me out. I need to calculate a duration with the following IF considered.

if "index" = 1 then date_replied - date created else
if "index > 1 then date_replied - date replied from the row prior (current index - 1)

I would love to do this in both a DAX and also within Power Query using custom column as i will be looking to average this duration out. 

Thanks in advance for any assistance provided.

Todd

[Samarth_18 · Accepted solution]

Hi @ToddMate ,

You could create a column as below:-

Column =
VAR current_index = 'Table (2)'[index]
VAR prev_index = current_index - 1
VAR current_ticket = [ticket_number]
VAR prev_date_rep =
    CALCULATE (
        MAX ( 'Table (2)'[date replied] ),
        FILTER (
            'Table (2)',
            'Table (2)'[index] = prev_index
                && 'Table (2)'[ticket_number] = current_ticket
        )
    )
VAR result =
    IF (
        current_index = 1,
        DATEDIFF ( 'Table (2)'[date created], 'Table (2)'[date replied], SECOND ),
        DATEDIFF ( prev_date_rep, 'Table (2)'[date replied], SECOND )
    )
VAR _Seconds = result
VAR _Minutes =
    INT ( _Seconds / 60 )
VAR _RemainingSeconds =
    MOD ( _Minutes, 60 )
VAR _Hours =
    INT ( _Minutes / 60 )
VAR _RemainingMinutes =
    MOD ( _Minutes, 60 )
VAR _Days =
    INT ( _Hours / 24 )
VAR _RemainingHours =
    MOD ( _Hours, 24 )
RETURN
    FORMAT ( _Days, "00" ) & ":"
        & FORMAT ( _RemainingHours, "00" ) & ":"
        & FORMAT ( _RemainingMinutes, "00" ) & ":"
        & FORMAT ( _RemainingSeconds, "00" )

Output:-

Note:- Minutes and seconds values are vary since i have not added seconds data in the dataset.