Calculating working hours between 2 timestamps

DAX DAX Calculations
1

Hi all,

It’s my first time posting so you can guess this has me stumped :slight_smile:

I need to create a calculation to show how many hours it took for IT to acknowledge a ticket (2 hours is the SLA), with the clock starting from 9am until 5pm (GMT) each working day (Monday - Friday).

The issue I have is that as the company is global, and tickets can be raised after 5pm and before 9am.

Example:

Ticket raised - 29/01/2019 19:30:00

Ticket acknowledged - 30/01/2019 10:30:00

Desired results = 1h 30 mins (9am - 10.30am)

Without the hours from 7.30pm until 9am removed I currently get 15h 0 mins

Any ideas?

2

Create either a calculated column or a Measure using the DATEDIFF function. All you do is use your Ticket Raised Field & Ticket Acknowledged Field and it will automatically do the work for you.

Thanks
Jarrett

Enterprise%20DNA%20Expert%20-%20Small

1 Like
3

The DATEDIFF doesn’t exclude weekends, holidays and offhours. Does it ?

4

Not automatically, but if you have something setup in your model for Weekends, Holidays & Offhours, you can add it to the Measure.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

5

Hi,

I tend to make these calculations in PowerQuery, given it has many standard options to calculate time.
What do you prefer.

Paul

Enterprise%20DNA%20Expert%20-%20Small