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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Utilization rate

I'm hoping that there is an easier way to go about this than what I have been trying to do. 
We have trucks that run 24/7. Within there, we have shifts that can start at anytime. The majority run less than 11 hours, but we have some that will span multiple days. I am wanting to know, for a given 24 hour period of time, what percent is connected to a shift. 

I have a column with shiftstartdatetime, and a column with shiftenddatetime. I also have a separate table for dates titled Date.

I can get the total number of hours in a shift, but it is relating that back to utilization, esp if I have a shift end in the morning and another pick up in the afternoon; or if one runs multiple days(across country)

 

I'd even take a hint at where to look. Figuring it is going to be a couple calculated columns first, then a measure.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Just going to aggregate to the week/month instead. We are looking for outliers of grossly underutilized resources and this will get us there just as well, if not better. 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Just going to aggregate to the week/month instead. We are looking for outliers of grossly underutilized resources and this will get us there just as well, if not better. 

Hi @Anonymous ,

I'm not clear about your requirement. If you need help, please share some sample data and expected results. Then we will understand clearly. If you have resolved it, please share your solution here. More people who have the same request will benefit from this thread.

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
edhans
Super User
Super User

We'd need to see some data to really work on this. Additionally, you should put all your column calculations in Power Query if possible, not in DAX. See links below for the reasons. As far as data, we'd also need to see some expected output.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

 

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

Yeah, I've done a fair amount with tricky time manipulation. I have a whole chapter in my book on Time, including Shift calculations although not exactly what you have going on. Also, there is a Utilization recipe. (DAX Cookbook).

 

Here are some links to some of the work I have done around time and such that hopefully will point you in the right direction.

Also always though, sample data, expected output and the logic to get from point A to B is the quickest way to get an answer. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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