Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Will_740
New Member

Average Time Measure

Hi,

 

Im looking for a measure to calculate an average time that my clients fall asleep at night. I have used the following DAX formula for wake up time which is satisfactory:

 

Sleep Onset Time Avg = FORMAT(CALCULATE(AVERAGE(FacTBLsleepwatch[Sleep Onset Time (hh:mm)])),"HH:mm")

 

This works for wake up time as they wake up on the same day between 6am and 9am and so an average somewhere between these points is correct.

 

However, it doesnt work for sleep onset in the evening. If they fall asleep at 11pm on one night, and 3am the next, the above measure seems to set these times in chronological order, and would come up with an average sleep onset value of 1pm (in the afternoon), rather than 1am (in the morning - which is the value I would want).

 

Does anyone know a way to allow DAX to identify when the value to be calcualted moves past the 24 hour clock to the next day. The data is saved in query as date/time in the single column for sleep onset time.

 

Any help would be appreciated.

 

Thanks 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

This was an interesting challenge, and the expression below should get your desired result.  It takes each row (onset time) and converts it to minutes.  If it is < 12 PM, it adds 24 hrs (in minutes) to it, before taking the average.  Once the average is calculated, it subtracts the 24 hrs back off (if >midnight), and converts the minutes back to hours and minutes.

 

Correct Average =
VAR avgtimeinminutes =
AVERAGEX (
Sleep,
VAR timeinminutes =
HOUR ( Sleep[Onset] ) * 60
+ MINUTE ( Sleep[Onset] )
VAR adjminutes =
IF ( timeinminutes < 720, timeinminutes + 1440, timeinminutes )
RETURN
adjminutes
)
VAR avgtimecorrection =
IF ( avgtimeinminutes < 1440, avgtimeinminutes, avgtimeinminutes - 1440 )
VAR avghour =
QUOTIENT ( avgtimecorrection, 60 )
VAR avgmin =
MOD ( avgtimecorrection, 60 )
RETURN
FORMAT ( TIME ( avghour, avgmin, 0 ), "h:mm am/pm" )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Microsoft Employee
Microsoft Employee

This was an interesting challenge, and the expression below should get your desired result.  It takes each row (onset time) and converts it to minutes.  If it is < 12 PM, it adds 24 hrs (in minutes) to it, before taking the average.  Once the average is calculated, it subtracts the 24 hrs back off (if >midnight), and converts the minutes back to hours and minutes.

 

Correct Average =
VAR avgtimeinminutes =
AVERAGEX (
Sleep,
VAR timeinminutes =
HOUR ( Sleep[Onset] ) * 60
+ MINUTE ( Sleep[Onset] )
VAR adjminutes =
IF ( timeinminutes < 720, timeinminutes + 1440, timeinminutes )
RETURN
adjminutes
)
VAR avgtimecorrection =
IF ( avgtimeinminutes < 1440, avgtimeinminutes, avgtimeinminutes - 1440 )
VAR avghour =
QUOTIENT ( avgtimecorrection, 60 )
VAR avgmin =
MOD ( avgtimecorrection, 60 )
RETURN
FORMAT ( TIME ( avghour, avgmin, 0 ), "h:mm am/pm" )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hey Pat 

 

This is great and question Is there a way to add seconds into this measure?

Hi Pat,
 
Thanks for taking the time to think about this, it worked really well!
 
Best Regards,
Will
lbendlin
Super User
Super User

Change your data model so the sleep onset is captured by a Datetime field, not just a time field.  Or have logic that checks if the date value is the same for your timestamps.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors