Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
rafiql
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

rafiql_0-1671611649654.png

and Fact table as

rafiql_0-1671613904950.png

 

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.

rafiql_2-1671612111869.png

 

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
Mahesh0016
Super User
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

View solution in original post

1 REPLY 1
Mahesh0016
Super User
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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors