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

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