Forum Discussion
Get durations from event data
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)
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.
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])
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)