Calculate lifecycle - Average

[Anonymous]

Hi everyone,

I want to find out how long the tickets in our system are open for on average.

Tickets have a CreatedDate and what I want to find out is how much time lapses between created and closed, and then calculate an average per month.

How would I structure this? It should be a combination of RefereneceNo (which is the ticket number), CreatedDate, ClosedDateTime and a calculation somehow to get the average?

Thank you for all your expertise!

[ichavarria]

Hi @Anonymous,

To calculate the average time it takes for a ticket to be closed, you'll need to:

1. Create a calculated column that calculates the time difference between the CreatedDate and ClosedDateTime columns.
2. Use a measure to calculate the average of the calculated column per month.

Here are the steps to create the calculated column:

1. Open Power BI and connect to your data source.

2. Click on the "Transform data" button to open Power Query Editor.

3. Select the table that contains the ticket data.

4. Click on the "Add column" tab in the ribbon and select "Custom column".

5. In the "Custom column" dialog, enter a name for the column, such as "TimeToClose".

6. In the "Custom column formula" field, enter the following formula:

   = if [ClosedDateTime] = null then null else [ClosedDateTime] - [CreatedDate]

   This formula checks if the ticket has been closed. If it has, it calculates the time difference between the CreatedDate and ClosedDateTime columns. If the ticket is still open, the formula returns null.

7. Click "OK" to create the new column.

Here are the steps to create the measure:

1. Click on the "Report" tab to return to the report view.

2. Click on the "New measure" button in the "Fields" pane.

3. In the formula bar, enter the following formula:

   AverageTimeToClose = AVERAGEX('table_name', 'table_name'[TimeToClose])

   Replace "table_name" with the name of the table that contains the ticket data.

4. Click on the "Modeling" tab and select "Month" from the "Date" dropdown to group the measure by month.

You can now add the AverageTimeToClose measure to your report, and it will display the average time it takes to close a ticket per month.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly