Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Find time between one fixed row and one variable row

Hello, I hope some can help me, i have a data table with this columns - TicketID - Ticket creation date - Last action - Date/time of last action   Actions can be things like giving a priority t...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    It looks like there existing more than one records where Action is set to "Set Priority to regular" per Ticket ID. If so, please try below suggestion. 

     

    First, add a calculated column in Table1.

    Index =
RANKX (
    FILTER ( 'Table1', Table1[TicketID] = EARLIER ( Table1[TicketID] ) ),
    Table1[Action date/time],
    ,
    ASC,
    DENSE
)

    Then, add measure [Average] into a card visual.

    Timediff =
VAR _CurrentActiontime =
    SELECTEDVALUE ( Table1[Action date/time] )
VAR _NextActiontime =
    CALCULATE (
        SELECTEDVALUE ( Table1[Action date/time] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[TicketID] ),
            Table1[Index]
                = MAX ( Table1[Index] ) + 1
        )
    )
VAR diff =
    IF (
        SELECTEDVALUE ( Table1[Action] ) = "Set priority to regular",
        DATEDIFF ( _CurrentActiontime, _NextActiontime, SECOND ),
        BLANK ()
    )
RETURN
    diff

    Average =
    AVERAGEX ( ALLSELECTED ( Table1 ), [Timediff] )

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Yes exactly, something like this: - Ticket A took 5 minutes between the set priority and following action - Ticket B took 3 minutes between the set priority and following action - Ticket C took 15 minutes between the set priority and following action The average has to be the average of 5, 3 and 15 minutes Many thanks for your help.
AlB's avatar
AlB
Community Champion
7 years ago

Anonymous

 

Try this measure in a card visual for instance:

 

AvgTimeCreation2Priority =
VAR _AuxTable =
    SUMMARIZECOLUMNS (
        Table1[TicketID];
        "Time2Next";
        VAR _CreationTime =
            VALUES ( Table1[ Ticket created] )
        VAR _NextActiontime =
            FIRSTNONBLANK (
                CALCULATETABLE (
                    VALUES ( Table1[ Action date/time] );
                    Table1[ Action date/time] > _CreationTime
                );
                1
            )
        VAR _TimeDiffMins = ( _NextActiontime - _CreationTime )* 24 * 60
        RETURN
            _TimeDiffMins
    )
RETURN
    AVERAGEX ( _AuxTable; [Time2Next] )

where Table1 is the name of your table. Do note that the result is in minutes (ex.  2 mins 30 seconds will show as 2,5). You can convert that into mins:secs format if you so need.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    This looks great and when i use it on a card visual i get data. The only doubt i have is, does this use the rows with "set priority to" as start date/time? Maybe it's my beginner knowledge of PowerBI but i don't understand how this measure does that.
  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Anonymous

     

    Sorry, you're right. I misread and was taking the creation time as initial time. This is the updated version. As you see you can use this pattern with minor variations to extract the time period you are interested in.

     

    AvgTimeSetPriority2Next =
VAR _AuxTable =
    SUMMARIZECOLUMNS (
        Table1[TicketID];
        "Time2Next";
        VAR _SetPriorityTime =
            CALCULATE (
                VALUES ( Table1[ Action date/time] );
                Table1[Action] = "Set priority to regular"
            )
        VAR _NextActiontime =
            FIRSTNONBLANK (
                CALCULATETABLE (
                    VALUES ( Table1[ Action date/time] );
                    Table1[ Action date/time] > _SetPriorityTime
                );
                1
            )
        VAR _TimeDiffMins = ( _NextActiontime - _SetPriorityTime ) * 24 * 60
        RETURN
            _TimeDiffMins
    )
RETURN
    AVERAGEX ( _AuxTable; [Time2Next] )
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Thank you for al your kind help, i really appreciate it. I have tried the updated version but now receive the error "A table of multiple values was supplied where a single value was expected" Do you have any idea what causes this?
  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Anonymous

     

    Hmmm... I ran a quick test and it's working fine. Maybe you have more than one row for a TicketID with Action "Set priority to regular"?