Need help with the data and calculation

[DiKi-I]

Hi,
I have a ticket table with ticket creation date, resolve date and close date and its history table with history of tickets with different status in the lifecycle of ticket.
Sample data attached.

https://docs.google.com/spreadsheets/d/1oJviOcZxtN_t0_-IfHgAiqPxRHkF-kJq/edit?usp=sharing&ouid=10852...

I need to find the different durations like how long between ticket open and resolved or how long between ticket active and resolved. Can you please help me with this data and calculation and how I can model this in power bi.



How I can create these calculation based on this data. 

[DiKi-I]

This is not what my requirement is I have to show the average time when the ticket state was was open->active
similary average time when ticket was active->resolve.

[Data-estDog]

You never mentioned average in your original post. You can go the direct dax route by using AverageX. 

'X' on a function calculates on a row by row basis before rolling it up into an aggregate. Precalculating the duration on a row level would still be more efficient. If you are dealing with a large number of rows on a highly used report you can do that later. Here is the DAX:

Avg Create to resolved = AVERAGEX(Sheet2, DATEDIFF(Sheet2[Create Date], Sheet2[resolved_date], MINUTE))

Please accept as solution to help others find quicker. Consider Kudos. 🙂

[DiKi-I]

I think I am not able to explain it properly.
I have to calculate the average time of a ticket when status changes from new to active based on starttime and endtime in the history table. Simlarly I have to calculate the average time of ticket when the status of ticket changes from active to resolve based on starttime and endtime. How I can do this dynamically using measure? I have history of tickets with its life cycle.

[Data-estDog]

So you are saying the Average Time of the day.... like 5:32 PM?

So 10 tickets had a status change at 5:30 and 10 other tickets had a status change at 5:34, so the average is 5:32?

If no, trying mocking up an example of the outcome you are looking for and posting it.

[DiKi-I]

Hi all,

I have history data of tickets and I want to calculate two kpis one is how many tickets was fixed in first contact and another is the average time to acknowledge the ticket when it was created.
Attached is the sample data. When a ticket arrives it gets assigned so the first record of the ticket will be used to find the average time to acknowledge the ticket and if a ticket has only one assignee/one record those will be first time fix.
Can someone help me with the dax. This data will only have resolved tickets history.

https://docs.google.com/spreadsheets/d/1BLONNk8-VkhHD5WGsFHchAKmfSdP7hNN/edit?usp=drive_link&ouid=10...

[Data-estDog]

In Transformation view, create a new calculated column and use the DateDiff calculation to produce the duration values. 

DATEDIFF function (DAX) - DAX | Microsoft Learn

Or, if you source data is in the db, and you have control over views, add the duration values to the views in question. Also using datediff (in SQL Server)