Skip to main content
cancel
Showing results for
Search instead 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
1 REPLY 1
Super User

## Helpful resources

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.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors