cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

## How to calculate max time and min time difference of datetime filed

Hello

I have date dimension with columns as Date, Month, and Year

and Fact table as

PLease help writing a dax function to calculate total hour when a date is selcted from the slicer.  if 3rd of march is selected, result would be 4min 26 second, we can ignore seconds, 4 mins woild be ok. but if hour is there it would X hr Y Min.

What I have done so far as could not find any built in function:

ClockHour =
VAR MaxDate = MAX(ST_Hourly_Setups[ScannedDate]) // I could not filter the date selected from slicer here. or it is the silcer date
VAR maxHR = HOUR(MaxDate)
VAR maxMin = MINUTE(MaxDate)

VAR MinDate = MIN(ST_Hourly_Setups[ScannedDate]) // I could not filter the date selected from slicer here or it is the slicer date
VAR minHR = HOUR(MinDate)
VAR minMin = MINUTE(MinDate)
RETURN (((maxHR*60) + maxMin)) - (((minHR*60) + minMin))

Very poor!! i know, any alternative???

1 ACCEPTED SOLUTION
Super User

>> You can try this way.

Clock Hour&Minutes =
VAR MaxDate = MAX('Table'[ScannedDate])
VAR MaxHours = HOUR(MaxDate)
VAR MaxMinutes = MINUTE(MaxDate)
VAR MaxinHours = (MaxHours*60)+MaxMinutes
//
VAR MinDate = MIN('Table'[ScannedDate])
VAR MinHours = HOUR(MinDate)
VAR MinMinutes = MINUTE(MinDate)
VAR MininHours = (MinHours*60)+MinMinutes

RETURN
MaxinHours-MininHours
Super User

>> You can try this way.

Clock Hour&Minutes =
VAR MaxDate = MAX('Table'[ScannedDate])
VAR MaxHours = HOUR(MaxDate)
VAR MaxMinutes = MINUTE(MaxDate)
VAR MaxinHours = (MaxHours*60)+MaxMinutes
//
VAR MinDate = MIN('Table'[ScannedDate])
VAR MinHours = HOUR(MinDate)
VAR MinMinutes = MINUTE(MinDate)
VAR MininHours = (MinHours*60)+MinMinutes

RETURN
MaxinHours-MininHours

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors