Question about Date Difference format.

[Anonymous]

Hello! This is my first post here. 

So, I'm working with a support-ticket database, which includes fields such as "ID number", "Client", "Creation Date", "Close Date", "Solving Agent", etc.

One of the metrics I'm willing to obtain is a graph that shows the Average Ticket Resolution Time of each of the agents. This is in order to check how fast are each of the employees working with their tickets.

How would you recommend to do that? My first attemp was:

1- Create a new column "creation-solution time" = 1*(DBNAME[Close_Date]-DBNAME[Creation_Date])

2- I obtained a column that shows the time it took the agent to solve the ticket since it was created, with the following format:

            -At Power Query: DD.HH:MM:SS

            -At Data (Power BI): a rational number > 0.00000

3- I tried creating a measure:

            -TimePerPerson = CALCULATE (AVERAGE ('DBNAME'[creation-solution time]) , 'DBNAME'[Agent])

But as you might imagine, that brough to nothing good.

My intention is to create a bar chart with each agent and their average resolution time. Do you have any other idea/experience to proceed in order to obtain this?

Thank you so much for reading! Hope you could help me!!

Franco.

[amitchandak]

@Anonymous , You can create a new column with diff in seconds

creation-solution time = Datediff(DBNAME[Creation_Date],DBNAME[Close_Date], second)

Then you can create a Avg measure

Avg = average(Table[creation-solution time])

You can new measure for display

Quotient([Avg ],3600) &":" & Quotient(mod([Duration],3600),60) &":" & Mod(mod([Duration],3600),60)