How to calculate duration from single column with multiple conditions

[jimminy]

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

[v-jianboli-msft]

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]

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]

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.

[jimminy]

problem solved, thank you so so much! I really appreciate the help.

Best,

J

[jimminy]

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

[amitchandak]

@jimminy , Create a new column

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