Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Find average duration between two dates

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 h...
Need Help
gmsamborn's avatar
gmsamborn
Super User
2 years ago

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.