Need help in DAX to calculate "End Time" based on scenarios

[Anonymous]

Hi All, 

I need help to calculate the time worked upon by Level 1 support team. 

Example Dataset:

Table1 showing tracking time of teams involved for incident ID - 001 

Incident Id	Owner	Team	Incident Logged	Start Time	End Time
001	zzzzz	Q1	8/7/2020 15:00	8/9/2020 16:45	8/9/2020 17:00
001	abc	L1	8/7/2020 15:00	8/9/2020 17:00	8/9/2020 17:50
001	aaaaa	E1	8/7/2020 15:00	8/9/2020 17:50	8/9/2020 18:00
001	xyz	L2	8/7/2020 15:00	8/9/2020 18:00	8/9/2020 19:00
001	abc	L1	8/7/2020 15:00	8/9/2020 19:00	8/9/2020 19:10
001	aaaaa	E1	8/7/2020 15:00	8/9/2020 19:10	8/9/2020 20:00

Where - 

L1	Level 1 Support
Q1	Intermediate Queue
E1	Escalation Queue
L2	Level 2 Support

So the aim is to 

1) Calculate the "END TIME" when incident has been transferred from L1 to E1 or L2. 

2) Create a Calculated column for days between "INCIDENT LOGGED" and "END TIME". 

Expected Output: 

Incident Id	Owner	Team	Incident Logged	Start Time	End Time	Expected Output
001	zzzzz	Q1	8/7/2020 15:00	8/9/2020 16:45	8/9/2020 17:00	Blank
001	abc	L1	8/7/2020 15:00	8/9/2020 17:00	8/9/2020 17:50	2 days
001	aaaaa	E1	8/7/2020 15:00	8/9/2020 17:50	8/9/2020 18:00	Blank
001	xyz	L2	8/7/2020 15:00	8/9/2020 18:00	8/9/2020 19:00	Blank
001	abc	L1	8/7/2020 15:00	8/9/2020 19:00	8/9/2020 19:10	Blank
001	aaaaa	E1	8/7/2020 15:00	8/9/2020 19:10	8/9/2020 20:00	Blank

Now the challenge here is the incident lifecycle can be of varying types as below - 

1	2	3	4	5	6	7	8
L1	L1	Q1	Q1	Q1	Q1	Q1	Q1
	Q1	L1	L1	L1	L1	L1	L1
			E1	L2	Q1	E1	E1
					L1	L2	L2
							L1
							E1

Please help to develop a robust solution to handle all kinds of scenario. 

@amitchandak ,@Greg_Deckler  - Please assist. 

[Anonymous · Accepted solution]

Hi @Anonymous 

I build a table like yours and add an index column in Power Query Editor to have a test.

From your statement I think you want to calculate the differences between Incident Logged and End Time by day, if Team is changing from L1 to E1 or L2. But your example make me confused, the Expected Output in the red box should be 2 rather than blank. If there is anything wrong in my understanding, please tell me.

My measure:

Measure = 
VAR _Team =
    MAX ( 'Table'[Team] )
VAR _Datediff =
    DATEDIFF ( MAX ( 'Table'[Incident Logged ] ), MAX ( 'Table'[End Time] ), DAY )
VAR _NextTeam =
    CALCULATE (
        MAX ( 'Table'[Team] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = MAX ( 'Table'[Index] ) + 1 )
    )
RETURN
    IF (
        AND ( _Team = "L1", OR ( _NextTeam = "E1", _NextTeam = "L2" ) ),
        _Datediff,
        BLANK ()
    )

 Result:

If this reply still couldn't help you solve your problem, please tell me more details such as your calculate logic and why the L1 in red box is unqualified. Or you can share your pbix file with me by your OneDrive for Business.

You can download the pbix file from this link: Need help in DAX to calculate "End Time" based on scenarios

Best Regards,

Rico Zhou

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

[Anonymous]

Thank you all for assistance, I am still testing the solution for all use cases we have. I will update soon. 

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

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

[mahoneypat · Accepted solution]

Please try these two column expressions, replacing Incidents with your actual table name. 

Overall End Time =
VAR firstE1L2 =
    CALCULATE (
        MIN ( Incidents[Start Time] ),
        ALLEXCEPT ( Incidents, Incidents[Incident Id] ),
        Incidents[Team] IN { "E1", "L2" }
    )
VAR lastL1 =
    CALCULATE (
        MAX ( Incidents[End Time] ),
        ALLEXCEPT ( Incidents, Incidents[Incident Id] ),
        Incidents[Team] = "L1",
        Incidents[End Time] <= firstE1L2
    )
VAR minL1 =
    CALCULATE (
        MIN ( Incidents[Start Time] ),
        ALLEXCEPT ( Incidents, Incidents[Incident Id] ),
        Incidents[Team] = "L1"
    )
RETURN
    IF ( AND ( Incidents[Start Time] = minL1, Incidents[Team] = "L1" ), lastL1 )


Days = if(not(ISBLANK(Incidents[Overall End Time])), Incidents[Overall End Time] - Incidents[Incident Logged ])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hey Pat, 

Thank you for the solution, it helped me to a greater extent. To make it fit in all below scenarios - 

1	2	3	4	5	6	7	8
L1	L1	Q1	Q1	Q1	Q1	Q1	Q1
	Q1	L1	L1	L1	L1	L1	L1
			E1	L2	Q1	E1	E1
					L1	L2	L2
							L1
							E1

I have modified the calculation as - 

Overall End Time =
VAR firstE1L2 =
CALCULATE (
MIN ( Incidents[Start Time] ),
ALLEXCEPT ( Incidents, Incidents[Incident Id] ),
Incidents[Team] IN { "E1", "L2" }
)
VAR lastL1 =
CALCULATE (
MAX ( Incidents[End Time] ),
ALLEXCEPT ( Incidents, Incidents[Incident Id] ),
Incidents[Team] = "L1",
Incidents[End Time] <= firstE1L2
)

VAR maxL1 =
CALCULATE (
MAX ( Incidents[End Time] ),
ALLEXCEPT ( Incidents, Incidents[Incident Id] ),
Incidents[Team] = "L1"
)

var endTime = if(ISBLANK(firstE1L2),maxL1,lastL1)

RETURN
IF ( AND ( Incidents[End Time] = endTime, Incidents[Team] = "L1" ), endTime )

[Anonymous · Accepted solution]

Hi @Anonymous 

I build a table like yours and add an index column in Power Query Editor to have a test.

From your statement I think you want to calculate the differences between Incident Logged and End Time by day, if Team is changing from L1 to E1 or L2. But your example make me confused, the Expected Output in the red box should be 2 rather than blank. If there is anything wrong in my understanding, please tell me.

My measure:

Measure = 
VAR _Team =
    MAX ( 'Table'[Team] )
VAR _Datediff =
    DATEDIFF ( MAX ( 'Table'[Incident Logged ] ), MAX ( 'Table'[End Time] ), DAY )
VAR _NextTeam =
    CALCULATE (
        MAX ( 'Table'[Team] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = MAX ( 'Table'[Index] ) + 1 )
    )
RETURN
    IF (
        AND ( _Team = "L1", OR ( _NextTeam = "E1", _NextTeam = "L2" ) ),
        _Datediff,
        BLANK ()
    )

 Result:

If this reply still couldn't help you solve your problem, please tell me more details such as your calculate logic and why the L1 in red box is unqualified. Or you can share your pbix file with me by your OneDrive for Business.

You can download the pbix file from this link: Need help in DAX to calculate "End Time" based on scenarios

Best Regards,

Rico Zhou

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

[Greg_Deckler]

@Anonymous - If I understand things correctly, you are looking for the difference in date/time between rows based upon specific criteria. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

[Anonymous]

Thanks @Greg_Deckler for your reply. I am looking at the post you have redirected. I wil get back to you soon on this. 

[mahoneypat]

Your example data only has rows for Incident 1, but your expected output shows blank for incident 1.  Does that incident meet the criteria for going from L1 to E1 or L2?  Can you highlight/specify the values that should be used to get the proposed column?

Regards,

Pat

[Anonymous]

@mahoneypat - Thanks for highlighting, I made a mistake posting the expected output. All rows should only be for "001". I have edited the post. 

In the example, you see it is forwarded from L1 to E1, so the "END TIME" corresponding to L1 should be considered. 

001

abc

L1

Incident Logged - 8/7/2020 15:00

8/9/2020 17:00

End Time - 8/9/2020 17:50

2 days

The reason it should be returning blank for other rows is report should reflect L1 efficiency. And while calculating average, blank is not considered.