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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AmandaMulryan
Frequent Visitor

Allotting time between dates to a particular date

I have X number of devices that track statuses over time. I used LAG() in SQL to get the previous status and status date so that I could take the difference between the two dates and find how long the device has been in that status. However, when I try to show how long the device was in each status on each day, I get numbers that don't make any sense because sometimes the device is in a status for a long period of time. How do I calculate the hours for a given device and status when the dates span across days? 

 

EX) Device 2

      Start of Status = 11/22/2022 10:00 AM 

      End of Status = 11/23/2022 8:00 AM 

      Total time in "Status" is 22 hours, but when showing it by day, it looks like the device had > 24 hours in one day. 

 

Any help on how to do this is appreciated.

3 REPLIES 3
Anonymous
Not applicable

Hi @AmandaMulryan ,

 

Take a look at datediff-function .

You can choose the interval to use when comparing dates.

 

Best Regards,

Jay

lbendlin
Super User
Super User

Do you want to compute this in DAX or in Power Query?

DAX

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors