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

Helper I

## Calculating time difference between two date and time columns

Hi,

I'm currently trying to create a dax formula to calculate the time difference between a date/time column named 'Created at' in the tickets table and a date/time column named 'Last Closed At' in the tickets table. I'm only including dates where the value is Yes for 'IsWorkingDay' in the Date Table, 'DateTable' . I'm also excluding the time between 18:00 and 08:00. My result should be in hours.

``````TimeDifference =
VAR StartDateTime = MIN('Tickets'[Created at])
VAR EndDateTime = MAX('Tickets'[Last closed At])
VAR StartDate = DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime))
VAR EndDate = DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime))
VAR WorkingDays =
COUNTROWS(
FILTER(
'DateTable',
'DateTable'[IsWorkingDay] = "Yes"
&& 'DateTable'[Date] >= StartDate
&& 'DateTable'[Date] <= EndDate
)
)
VAR StartHour = HOUR(StartDateTime)
VAR EndHour = HOUR(EndDateTime)
VAR StartMinute = MINUTE(StartDateTime)
VAR EndMinute = MINUTE(EndDateTime)
VAR TotalHours =
IF(
StartDate = EndDate,
IF(
StartHour >= 8 && StartHour < 18 && EndHour >= 8 && EndHour < 18,
(EndHour - StartHour) + (EndMinute - StartMinute) / 60,
IF(
StartHour >= 8 && StartHour < 18,
(18 - StartHour) + (0 - StartMinute) / 60,
IF(
EndHour >= 8 && EndHour < 18,
(EndHour - 😎 + (EndMinute - 0) / 60,
0
)
)
),
IF(
StartHour >= 8,
(18 - StartHour) + (0 - StartMinute) / 60,
0
) + IF(
EndHour < 18,
(EndHour - 😎 + (EndMinute - 0) / 60,
0
)
)
RETURN
WorkingDays - 1 + TotalHours / 24``````

Formula seems to work ok for the entries on the same day but for different days as below, the results are not correct.

Any help much appreciated.

1 ACCEPTED SOLUTION
Super User
Super User

Announcements

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors