Get durations from event data

[rudivonstaden]

I am trying to analyse my data in Power BI to work out how long we spend on tickets. The data sits in two tables.

The first table (Issue Timings) shows when an issue is created or closed.

The second (Issue Events) records when a ticket is moved between pipelines.

I would like to know how much total time a ticket spends in "In Progress". There are some complications. A ticket may be created in "In Progress", so there may not be an event where it is moved to "In Progress". A ticket may also be closed in "In Progress" so there's no event when it's moved from the pipeline. Also, the ticket may be moved to and from "In Progress" multiple times.

Any advice on how to break this down to get the total duration per issue in Power BI (using M and/or DAX)? I have some sample data here if you'd like to take a look.

[v-zhenbw-msft]

Hi @rudivonstaden ,

We are a little confused about your calculation logic. If you can describe it more clearly, it will be helpful to us.

Do you want to calculate the duration of In progress? For example, the issue number is 4001, is the duration the difference between the second row and third row?

Or is the difference between the second and third rows plus the difference between the fourth and fifth rows?

How to calculate if Issue Number only has one In Progress, like Issue Number 4000. Do we need to subtract the Created_DateTime of the Issue Timings Table from Time of the Issue Events Table?

It will be helpful if you can show us the exact expected result based on the tables.

Best regards,

Community Support Team _ zhenbw

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

[rudivonstaden]

Thanks for your reply, and apologies for not explaining clearly up front. I think you did understand pretty well, and you have identified some of the challenges that make this somewhat complicated. In working out how to explain it I think I've figured out a workflow in Excel to get the durations (more or less). Now I just need to figure out how to do the same things in Power BI.

Step 1: Reduce the columns in the the Issue Timings table so that they only have an Issue Number, DateTime and Event column (solved by selecting the Created and Closed columns and Unpivoting in Power BI):

Step 2: Append data from Issue Events. We can ignore the "moved from" column in Issue Events and append only the "moved to" column to get this (in Power BI, delete "moved from" column, rename to match Issue Timings table and append Issue Events to Issue Timings):

Step 3: add a column to indicate the pipeline the issue is in at that DateTime. The pseudocode logic is as follows: 

If [Current Event] is "Created"
   If [Next Event] is "In Progress" Then Pipeline is "Up Next"
   Else If [Next Event] is "Up Next" Then Pipeline is "New Issues"
   Else Pipeline is "In Progress"
Else Pipeline is [Current Event]

As an Excel formula it looks like this (in cell D2):

=IF(C2="Created",IF(C3="In Progress","Up Next",IF(C3="Up Next","New Issues","In Progress")),C2) 

Step 4: There's now enough information to calculate the time spent in each pipeline, not just the the "In Progress" pipeline. The formula I used to calculate duration in hours was basically

If [Next Issue Number] = [Current Issue Number] Then Duration = ([Next DateTime] - [Current DateTime])*24
Else Duration = 0

I think from here it will be fairly trivial (I hope) to sum the total time filtered by issue number and pipeline in DAX.

I've made some progress on doing this in Power BI, but I'm struggling with step 3. How can I reference the next event (event in the next row)? I'll have to also reference the next issue number and the next datetime in step 4.

Current progress (including pbix) is here.

[rudivonstaden · Accepted solution]

I have managed to work it out in DAX after adding an index column in M. My query is now:

let
    Source = Excel.Workbook(File.Contents("D:\tmp\timing_data\timing_data.xlsx"), null, true),
    Issues_Sheet = Source{[Item="Issues",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Issues_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Issue Number", Int64.Type}, {"Created", type datetime}, {"Closed", type datetime}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Issue Number"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Event"}, {"Value", "DateTime"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Issue Number", "DateTime", "Event"}),
    #"Appended Query" = Table.Combine({#"Reordered Columns", #"Issue Events"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Issue Number", Order.Ascending}, {"DateTime", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Index",{"Index", "Issue Number", "DateTime", "Event"})
in
    #"Reordered Columns1"

In DAX I added a calculated column for Pipeline:

Pipeline = 
VAR NextEvent = LOOKUPVALUE('Issues Timings'[Event],'Issues Timings'[Index],'Issues Timings'[Index]+1)
RETURN IF('Issues Timings'[Event] = "Created",
 IF(NextEvent = "In Progress","Up Next",
 IF(NextEvent = "Up Next", "New Issues",
 "In Progress" )),
 'Issues Timings'[Event])

and another calculated column for duration:

Duration = 
VAR StartDateTime = 'Issues Timings'[DateTime]
VAR StartTime = TIME(8,0,0)
VAR EndTime = TIME(17,0,0)
VAR NextIssue = LOOKUPVALUE('Issues Timings'[Issue Number],'Issues Timings'[Index],'Issues Timings'[Index]+1)
VAR EndDateTime = IF(NextIssue = 'Issues Timings'[Issue Number], LOOKUPVALUE('Issues Timings'[DateTime],'Issues Timings'[Index],'Issues Timings'[Index]+1),'Issues Timings'[DateTime])
VAR NetWorkDays = 
COUNTROWS (
 FILTER (
 ADDCOLUMNS ( CALENDAR ( StartDateTime, EndDateTime ), "Day of Week", WEEKDAY ( [Date], 1 ) ),
 [Day of Week] <> 1
 && [Day of Week] <> 7
 && NOT(CONTAINS(Holidays,Holidays[Date],[Date]))
 )
)
RETURN 
IF(OR(EndTime<StartTime,EndDateTime<=StartDateTime),0,
    (NetWorkDays
    -(1
    *IF(MOD(StartDateTime,1)>EndTime,1,
        (MAX(StartTime,MOD(StartDateTime,1))-StartTime)
        /(EndTime-StartTime)))
    -(1
    *IF(MOD(EndDateTime,1)<StartTime,1,
        (EndTime-MIN(EndTime,MOD(EndDateTime,1)))
        /(EndTime-StartTime))))
    *(EndTime-StartTime)*24)

The Duration column applies the logic in this page to calculate the working hours for each Issue in each Pipeline. The solution file is here.