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

AlB's avatar
AlB
Community Champion
7 years ago

Anonymous

What do you exactly mean by 'giving priority to a ticket'? Is this when 'Action' is set to "Set priority to regular"?

Do you want to see the time between when 'Action' is set to "Set priority to regular" and whatever comes immediately afterwards for a specific TicketID?

Providing an example with your sample data would probably help clarify 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago
@AIB Yes i mean exactly what you are saying. With giving a priority to a ticket i indeed mean the action set priority to regular in the example table. And i indeed want the time between when 'Action' is set to "Set priority to regular" and whatever comes immediately afterwards that for a specific ticket ID. I want to give you all the sample data that you need, but i don't know what data you want extra besides the example table that i allready posted above.
  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Anonymous

     

    And how would the average have to be calculated exactly? Is it the average of that period of time you just described across all TicketIDs?

     

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