Forum Discussion

ToddMate's avatar
ToddMate
Helper II
4 years ago
Solved

Calculation Duration with IF statement

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

 

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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.

     

1 Reply

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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.