Difference between two times when time goes past midnight

[Creative_tree88]

Hi all - I have a dataset, and I'm trying to calculate the time between two dates.  The basic 'DATEDIFF' in minutes DAX works fine until I get to a line of data which is looking at a time which starts before midnight and ends after midnight, as in the example below.  I get minus numbers.  What's the bext way to deal with this?  Looking for a solution - thanks all.

[123abc · Accepted solution]

DAX formula you can use to create a calculated column that displays the time difference in the format "X hours Y minutes":

Time Diff Formatted =
VAR TotalMinutes = [Time Diff Req to Event]
VAR Hours = INT(TotalMinutes / 60)
VAR Minutes = MOD(TotalMinutes, 60)
RETURN
Hours & " hours " & Minutes & " minutes"

For example:

• If the difference is 56 minutes, it will show as "0 hours 56 minutes".
• If the difference is 125 minutes, it will show as "2 hours 5 minutes".

[Kedar_Pande]

@Creative_tree88 ,

DAX formula:
Time Difference (Minutes) =
VAR ReqTime = MAX('Table'[Req Time Formatted])
VAR EventTime = MAX('Table'[Event Time Formatted])
RETURN
IF(
ReqTime > EventTime,
DATEDIFF(EventTime, ReqTime, MINUTE),
DATEDIFF(EventTime, ReqTime + TIME(24, 0, 0), MINUTE)
)

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande

[_AAndrade]

Hi @Creative_tree88,

If you use all date with hours you can solve your problem.
Take a look at my example:

I'm using this dAX measure:

[bhanu_gautam]

@Creative_tree88 , Try using 

TimeDifference =
VAR StartTime = [StartTimeColumn]
VAR EndTime = [EndTimeColumn]
RETURN
IF (
EndTime < StartTime,
DATEDIFF(StartTime, EndTime + 1, MINUTE),
DATEDIFF(StartTime, EndTime, MINUTE)
)

[123abc]

in your sample negitive value a occure bcz " event time" corsses midnitght time, and BI is define it as a negative difference btwn two time.

first of all you need to ensur both time REQ TIME and EVENT TIME are in valid time format then to sovle the issue taht where the event time is past midnight we can change the logic by adding  24 hours to the event time if its samllar then the request time.

pleaase try this dax:

Time Diff Req to Event =
VAR ReqTime = VALUE(HOUR([Req Time Formatted]) * 60 + MINUTE([Req Time Formatted]))
VAR EventTime = VALUE(HOUR([Event Time Formatted]) * 60 + MINUTE([Event Time Formatted]))
VAR AdjustedEventTime = IF(EventTime < ReqTime, EventTime + 1440, EventTime) -- 1440 minutes in 24 hours
RETURN AdjustedEventTime - ReqTime

hopefully this solve the issue.

that you don’t get negative values when the event crosses midnight.

[Creative_tree88]

@123abc that works really well.  How do I convert the resulting calculated column into the format hours and minutes.  So, if it was 56 minutes...it shows 0 hours 56 minutes etc etc.  Thank you!

[123abc]

If this solved your prob please accept it as a solution so that it will help other community members

Regards:

Your Kudoed

Ali Abbas

[123abc · Accepted solution]

DAX formula you can use to create a calculated column that displays the time difference in the format "X hours Y minutes":

Time Diff Formatted =
VAR TotalMinutes = [Time Diff Req to Event]
VAR Hours = INT(TotalMinutes / 60)
VAR Minutes = MOD(TotalMinutes, 60)
RETURN
Hours & " hours " & Minutes & " minutes"

For example:

• If the difference is 56 minutes, it will show as "0 hours 56 minutes".
• If the difference is 125 minutes, it will show as "2 hours 5 minutes".

[Creative_tree88]

@123abc that's great - many thanks.  I'd like to show the average on a card, but obviously power BI can't show this as it's a string.  How can I show the average in format, as example '1h:50m' - on a card??  I could show this in minutes, as per original calculation, but for the user I really think they'd prefer to see it in hours and minutes.  Many thanks!  

[Anonymous · Accepted solution]

Hi @Creative_tree88 

Thanks for the reply from 123abc , bhanu_gautam , _AAndrade  and Kedar_Pande .

If you need to display the average time in the card in the format of HH:MM, the following test is for your reference:

Create a measure as follows

average = 
VAR _sum = SUM('Table'[Time Diff Req to Event])
VAR _count = COUNTROWS('Table')
VAR _averageSecond = DIVIDE(_sum, _count) * 60
VAR _NumberOfHours = QUOTIENT(_averageSecond, 3600)
VAR _NumberOfMinutes = QUOTIENT( MOD(_averageSecond, 3600), 60)
RETURN
FORMAT(_NumberOfHours, "[hh]") & ":" & FORMAT(_NumberOfMinutes, "[mm]")

Output:

Best Regards,
Yulia Xu

If our answers helps, then please consider Accept them as the solution to help the other members find it more quickly. Thank you!

[_AAndrade]

Use the FORMAT function https://learn.microsoft.com/en-us/dax/format-function-dax

Example: FORMAT( 'Your Measure', "hh:nn")