Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
EZRIDR01
Regular Visitor

Time Calculation

‎06-05-2020 08:54 PM

Sorry if this is very rudimentary, but I am new at this and struggling.

 

What is the DAX to calculate the difference in time, or the amount of hours worked for this data? 

 

Essentially I want...

Time Worked = (End Time - Start Time - Personal Time)

 

Time Table.JPG

Message 1 of 5
1,304 Views
0
1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

‎06-06-2020 06:32 PM

The other responses are good ways to do it in DAX after you've loaded the table, but if you want to do it in the query editor you can add a custom column with this formula

 

=Duration.TotalHours([End Time (Military Time)] - [Start Time (Military Time)]) - [Personal Time Away During Shift (Hours)]

 

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

Message 4 of 5
1,210 Views
4 REPLIES 4
mahoneypat
Microsoft Employee
Microsoft Employee

‎06-06-2020 06:32 PM

The other responses are good ways to do it in DAX after you've loaded the table, but if you want to do it in the query editor you can add a custom column with this formula

 

=Duration.TotalHours([End Time (Military Time)] - [Start Time (Military Time)]) - [Personal Time Away During Shift (Hours)]

 

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


Message 4 of 5
1,211 Views
EZRIDR01
Regular Visitor
In response to mahoneypat

‎06-08-2020 05:14 PM

MahoneyPat- Thank you!  This is what I was looking to accomplish.   Apologies to the others for not using the proper terminology (DAX vs M). 

 

I appreciate everyones efforts. 😊

Message 5 of 5
1,172 Views
0
lbendlin
Super User
Super User

‎06-06-2020 05:08 AM

Assuming you want the result in hours:

 

Time Worked = (End Time - Start Time)*24 - Personal Time

Message 3 of 5
1,249 Views

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All