Meantime to Close Column

[dommyw277]

Hi, im trying to create a columnn for meantime to close. So if an incident is open, tracking each one on closure time.
I have a created and resolved fields eg createddatetime and resolveddatetime. Any ideas?

[PavanLalwani · Accepted solution]

To calculate the Mean Time to Close (MTTC) in Power BI, you can create a calculated column that calculates the time difference between CreatedDateTime and ResolvedDateTime for each incident. Here’s how you can set it up:

Step 1: Create a New Calculated Column

1. In your Power BI report, go to the table where your incident data is stored.

2. Click on Modeling in the toolbar, then select New Column.

3. Enter the following DAX formula:

   DAX

   Copy code

   MTTC_Hours = IF( ISBLANK([ResolvedDateTime]), BLANK(), DATEDIFF([CreatedDateTime], [ResolvedDateTime], HOUR) )
   • Explanation:
     • DATEDIFF([CreatedDateTime], [ResolvedDateTime], HOUR) calculates the difference in hours between the creation and resolution times.
     • The IF condition checks if the ResolvedDateTime is blank (meaning the incident is still open) and, if so, returns a blank value instead of a time difference.

Step 2: Customize Time Units if Needed

If you’d prefer the time difference in minutes or days, change the last argument in DATEDIFF:

• For minutes: replace HOUR with MINUTE
• For days: replace HOUR with DAY

Step 3: Calculate the Average Mean Time to Close (Optional)

If you want an overall average MTTC, create a Measure instead:

1. Go to Modeling and select New Measure.

2. Enter this formula:

   DAX

   Copy code

   Average_MTTC_Hours = AVERAGE('YourTable'[MTTC_Hours])

   Replace 'YourTable' with the name of your table. This measure will give you the average time to close across all incidents that have been resolved.

With this setup, you’ll have both a per-incident MTTC and an overall average MTTC for closed incidents.

If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!

[Kedar_Pande · Accepted solution]

@dommyw277 

New Column:

MeanTimeToClose = 
IF(
ISBLANK('YourTable'[ResolvedDatetime]),
BLANK(),
DATEDIFF('YourTable'[CreatedDatetime], 'YourTable'[ResolvedDatetime], MINUTE)
)

You can aggregate this column (e.g., average) in your reports to get the mean time to close incidents.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[PavanLalwani · Accepted solution]

To calculate the Mean Time to Close (MTTC) in Power BI, you can create a calculated column that calculates the time difference between CreatedDateTime and ResolvedDateTime for each incident. Here’s how you can set it up:

Step 1: Create a New Calculated Column

1. In your Power BI report, go to the table where your incident data is stored.

2. Click on Modeling in the toolbar, then select New Column.

3. Enter the following DAX formula:

   DAX

   Copy code

   MTTC_Hours = IF( ISBLANK([ResolvedDateTime]), BLANK(), DATEDIFF([CreatedDateTime], [ResolvedDateTime], HOUR) )
   • Explanation:
     • DATEDIFF([CreatedDateTime], [ResolvedDateTime], HOUR) calculates the difference in hours between the creation and resolution times.
     • The IF condition checks if the ResolvedDateTime is blank (meaning the incident is still open) and, if so, returns a blank value instead of a time difference.

Step 2: Customize Time Units if Needed

If you’d prefer the time difference in minutes or days, change the last argument in DATEDIFF:

• For minutes: replace HOUR with MINUTE
• For days: replace HOUR with DAY

Step 3: Calculate the Average Mean Time to Close (Optional)

If you want an overall average MTTC, create a Measure instead:

1. Go to Modeling and select New Measure.

2. Enter this formula:

   DAX

   Copy code

   Average_MTTC_Hours = AVERAGE('YourTable'[MTTC_Hours])

   Replace 'YourTable' with the name of your table. This measure will give you the average time to close across all incidents that have been resolved.

With this setup, you’ll have both a per-incident MTTC and an overall average MTTC for closed incidents.

If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!