Find average duration between two dates

[Anonymous]

Hello,

I am working with some IT Incident Data and we are looking to get a field created for the average time between the tickets created date and resolved date. I have done some research and I have a few steps done but I'm having trouble with the actual averaging part.

We have "Created On" and "ResolvedDateTime". I was able to find a formula online to find the date difference between the two

if [ResolvedDateTime] is null then Duration.Days(DateTime.LocalNow()-[Created On]) else Duration.Days([ResolvedDateTime]-[Created On])

This works great. It shows a whole number difference between the two dates. Ideally I would like to get the difference down to days, hours, and minutes but that can wait. Now I'm trying to find the average amount of time from this new 'Date Difference" column. I did some additional research and found a similar post on this forum and I think I am close.

Average Open Duration = AVERAGE(Data[Date Difference])

I think this would technically work, but the model we are using has incidents that do not have resolution yet, therefore the ResolvedDateTime field is blank, and because of that I'm getting the below error.

Any ideas on how I can get either my formula to change the zeros, or get a similar 2nd formula that knows what to do with the zeros? In a similar vein, I would like the "Date Difference" to show Days, Hours, and Minutes if possible. Not entirely sure how much that changes the formula but wanted to include that just in case.

[gmsamborn]

Hi @Anonymous 

I'm not sure why that custom column didn't work.  Instead I created a column using your IF logic and then calculated days based on that date.

let
    Source = Excel.Workbook(File.Contents("C:\Power_BI\PBI Community\Find average duration between two dates\SampleData.xlsx"), null, true),
    Tickets_Sheet = Source{[Item="Tickets",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Tickets_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TicketID", Int64.Type}, {"Created On", type date}, {"x", Int64.Type}, {"ResolvedDateTime", type date}, {"Other field", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"x"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [ResolvedDateTime] = null then DateTime.LocalNow() else [ResolvedDateTime]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Days", each Duration.Days( [Custom] - [Created On] )),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Days", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Custom"})
in
    #"Removed Columns1"

Ignore anything above #"Added Custom".

Let me know if you have any questions.