cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Average time by hours and subtract 7 days

Hello, how can i do the average time by hours, minutes and seconds then subtract 7 days.

That's because the system stay for at least 7 days the ticket open after the support close, this why i need to subtract 7 days.

1 ACCEPTED SOLUTION
Helper I

@GeanFerreira ,Make sure that the columns you are using for 'OpenTimestamp'(created ) and 'CloseTimestamp'(closed) are of the correct data type (datetime)

Check for Empty or Null Values:

Make sure there are no empty or null values in the 'OpenTimestamp' and 'CloseTimestamp' columns, as this could also cause data type errors.

Here i can see there are empty or null values in closed column, so you're getting error in this step  (('BaseGeral'[closed])). make sure to remove them and try again.

If you find this helpful, please provide a kudo and mark it as an accepted solution.

4 REPLIES 4
New Member

I see now, that's fix the problem. Thank you!

Helper I

Hello @GeanFerreira ,

• Considering  timestamps for ticket creation and close events in the visual. follow the below steps  to calculate the average time spent on a ticket and then subtract 7 days

1. Create a Calculated Column for Ticket Duration:

• In your dataset, create a calculated column that calculates the duration of each ticket in seconds. You can use a formula like this:

below formula calculates the difference in seconds between the close and open timestamps for each ticket.

``````Ticket Duration (Seconds) = (EARLIER('Table'[CloseTimestamp]) - 'Table'[OpenTimestamp]) * 86400
​``````

2.Create a Measure for Average Duration:

Next, create a measure to calculate the average duration of all tickets in seconds:

``````Average Ticket Duration (Seconds) = AVERAGE('Table'[Ticket Duration (Seconds)])
​``````

3.Convert Average Duration to Hours, Minutes, and Seconds:

``````Average Ticket Duration (Hours) = INT([Average Ticket Duration (Seconds)] / 3600)
Average Ticket Duration (Minutes) = INT(MOD([Average Ticket Duration (Seconds)], 3600) / 60)
Average Ticket Duration (Seconds) = MOD([Average Ticket Duration (Seconds)], 60)``````

These measures extract hours, minutes, and seconds from the average duration.

4.Subtract 7 Days from the Average Duration:

below measure subtracts 7 days' worth of hours from the average duration

``Average Ticket Duration (Hours) - 7 Days = [Average Ticket Duration (Hours)] - (7 * 24)``

Now, you have measures that calculate the average ticket duration in hours, minutes, and seconds and another measure that subtracts 7 days from the average duration

If you find this helpful, please provide a kudo and mark it as an accepted solution.

New Member

Thank you for trying to help me, but when i tried the step one to create a calculated column, i found an error

The parameter isn't the right type

Helper I

@GeanFerreira ,Make sure that the columns you are using for 'OpenTimestamp'(created ) and 'CloseTimestamp'(closed) are of the correct data type (datetime)

Check for Empty or Null Values:

Make sure there are no empty or null values in the 'OpenTimestamp' and 'CloseTimestamp' columns, as this could also cause data type errors.

Here i can see there are empty or null values in closed column, so you're getting error in this step  (('BaseGeral'[closed])). make sure to remove them and try again.

If you find this helpful, please provide a kudo and mark it as an accepted solution.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.