Forum Discussion

jimminy's avatar
jimminy
Frequent Visitor
4 years ago

How to calculate duration from single column with multiple conditions

Hi all,

 

Looking for some help as I am still relatively new to Power BI.

 

I am aiming to calculate the duration a ticket has been with a team in the latest iteration. See data below:

 

Ticket ID   Start date/time        Team       Group Rank
1 20/05/2022 13.30   Team 1        1
1 20/05/2022 13.33   Team 2        2
1 22/06/2022 13.56   Team 2        3
1 22/06/2022 13.56   Team 3        4
1 23/06/2022 09.24   Team 3        5
1 23/06/2022 09.26   Team 2        6
1 27/06/2022 15.45   Team 2        7

 

So what I would be looking to calculate is essentially the time from Group Rank = 6 to today to understand how long a ticket has been with Team 2. To make things more complicated I have to do this for every Ticket ID and of course the number of handovers varies from ticket to ticket.

 

I have created a column calculating the duration between steps, but I'm not sure how I can "stop" a summation when a Team # changes.

 

Any input would be greatly appreciated 🙂

 

Best,

J

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    jimminy , Create a new column

     

    datediff([start datetime], minx(filter(Table, [Ticket ID] =earlier([Ticket ID])  && [start datetime] > earlier([start datetime]) ), [start datetime] ) , second)

  • jimminy's avatar
    jimminy
    Frequent Visitor
    4 years ago

    Hi amitchandak 

     

    Thanks for the reply. So this formula will give me a new column with the duration between steps, which is something I already have.

     

    The result I'm looking for is to show a table like the below:

    ticket ID           duration in team since last handover               total duration in team
    1  
    2  
    3  

     

    I've been able to calculate the total duration a ticket has been in a team, but the duration since last handover is something I'm struggling with, i.e. the duration from Group Rank 6 to Today mentioned in the first post.

     

    I hope this clarifies! 

     

    Best,

    J

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi jimminy ,

     

    Please try:

    duration in team since last handover =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[   Start date/time    ] ),
        FILTER ( 'Table', [Ticket ID] = EARLIER ( 'Table 2'[Ticket ID] ) )
    ) //max date
VAR _b =
    CALCULATE (
        MAX ( 'Table'[    Team    ] ),
        FILTER (
            'Table',
            [Ticket ID] = EARLIER ( 'Table 2'[Ticket ID] )
                && [   Start date/time    ] = _a
        )
    ) //last team
VAR _c =
    CALCULATE (
        MAX ( 'Table'[   Start date/time    ] ),
        FILTER (
            'Table',
            'Table'[Ticket ID] = EARLIER ( 'Table 2'[Ticket ID] )
                && [    Team    ] <> _b
        )
    ) //max date before last team
VAR _d =
    CALCULATE (
        MIN ( 'Table'[   Start date/time    ] ),
        FILTER (
            'Table',
            [Ticket ID] = EARLIER ( 'Table 2'[Ticket ID] )
                && [    Team    ] = _b
                && [   Start date/time    ] >= _c
        )
    ) //last team start date
RETURN
    DATEDIFF ( _d, _a, MINUTE )

     

    Output:

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • jimminy's avatar
      jimminy
      Frequent Visitor
      4 years ago

      Hi v-jianboli-msft ,

       

      Thanks so much for your reply.

       

      This is bringing me much closer. But the final output should be a summation of the last instance where the ticket is in the same team.

       

      e.g. your ouput is showing only the time between the last two rows, but both are team 2. I would like to see the time since it moved from Team 3 to Team 2. So in the example above it would be the time from row 6 until today.

       

      Does this make sense?

       

      Best,

      J

      • v-jianboli-msft's avatar
        v-jianboli-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi jimminy ,

         

        Please try:

        duration in team since last handover =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[   Start date/time    ] ),
        FILTER ( 'Table', [Ticket ID] = EARLIER ( 'Table 2'[Ticket ID] ) )
    ) //max date
VAR _b =
    CALCULATE (
        MAX ( 'Table'[    Team    ] ),
        FILTER (
            'Table',
            [Ticket ID] = EARLIER ( 'Table 2'[Ticket ID] )
                && [   Start date/time    ] = _a
        )
    ) //last team
VAR _c =
    CALCULATE (
        MAX ( 'Table'[   Start date/time    ] ),
        FILTER (
            'Table',
            'Table'[Ticket ID] = EARLIER ( 'Table 2'[Ticket ID] )
                && [    Team    ] <> _b
        )
    ) //max date before last team
VAR _d =
    CALCULATE (
        MIN ( 'Table'[   Start date/time    ] ),
        FILTER (
            'Table',
            [Ticket ID] = EARLIER ( 'Table 2'[Ticket ID] )
                && [    Team    ] = _b
                && [   Start date/time    ] >= _c
        )
    ) //last team start date
RETURN
    DATEDIFF ( _d, TODAY (), MINUTE )

        Output:

         

        Best Regards,

        Jianbo Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.