cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kasenotic
Frequent Visitor

I am working with shifts and I want to only calculate the total day by 16 hours between 2 shifts

Hi 

I need help with a problem. I have a client who is working 6 days a week they don't work sundays so it isn't 16hrs for 7 days a week. It is

[16 hours for Monday - Friday] 

[only 8 hours for Saturday] 

[0 hrs for Sunday]

 

In other words:
Monday        =       Morning Shift (5am - 1pm) ; Afternoon Shift (1pm - 9pm) = 16
Tuesday        =       Morning Shift (5am - 1pm) ; Afternoon Shift (1pm - 9pm) = 16
Wednesday   =      Morning Shift (5am - 1pm) ; Afternoon Shift (1pm - 9pm) = 16
Thursday       =      Morning Shift (5am - 1pm) ; Afternoon Shift (1pm - 9pm) = 16
Friday            =      Morning Shift (5am - 1pm) ; Afternoon Shift (1pm - 9pm) = 16
Saturday        =      Morning Shift (5am - 1pm) =  8
Sunday          =      0

 

How can I detect these shift and Saturday only morning shift with that time. I also want to create 2 separate graphs morning and afternoon shifts but calculating them with the 8 hours shift in a 16 hour day shift for Monday to Friday and Saturday only 8 hours for the morning shift and 8 hours within the working day and not calculate in 24 hour

1 ACCEPTED SOLUTION

@kasenotic See if the attached (below sig) file gives you any joy. Basically created a couple columns to calculate the total duration of each row in minutes and then the total duration in each shift in minutes.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

9 REPLIES 9
kasenotic
Frequent Visitor

@Greg_Deckler 

I tried it but I am having problem with my hours (hh:mm) time columns as well as my date column.. It's in the correct format but I am getting errors when the day is >9 for example it can only read day from Janury 1 - 9 but by the 10th and up it's an error. I'm not sure if Power Bi is confused reading the Day / Month / Year into Month / Day Year 

 

any thoughts to solve this problem??

 Error.PNG

@kasenotic Looks like it did a change type to Duration. I wouldn't, I would leave it is Text.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Alright, I'll work with this way instead, thank you for your help! 🙂

kasenotic
Frequent Visitor

@Greg_Deckler 

https://drive.google.com/file/d/1TI3x6pM0SbPoyatVucFRINS1LRUMWJ5i/view?usp=sharing

Hi, this is the data sorry I thought I pasted the data thank you! let me know if anything

I've got a problem too with hours over 24 I keep getting an error when I change the hh:mm to duration 

Greg_Deckler
Super User
Super User

@kasenotic Would need sample data and expected output to be more specific but you will need WEEKDAY to get the day of the week. You will need HOUR and maybe TIMEVALUE functions otherwise. I have a Shifts calculation in my book, DAX Cookbook, Chapter 3, Recipe 6. You can grab the code here: gdeckler/DAXCookbook (github.com)

 

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

 

Here is a sample data I'm working with. I have a list of machines operating (activity) in idle, off, active and, missing:

 

I need to do something like this

Monday = Total Active + Total Idle + Total Missing + Total Off = 16
Tuesday = Total Active + Total Idle + Total Missing + Total Off = 16
Wednesday = Total Active + Total Idle + Total Missing + Total Off = 16
Thursday = Total Active + Total Idle + Total Missing + Total Off = 16
Friday = Total Active + Total Idle + Total Missing + Total Off = 16
Saturday = Total Active + Total Idle + Total Missing + Total Off = 8
Sunday = 0 (edited) 8

 

and that this has too 

 

Monday        =       Morning Shift (5am - 1pm) ; Afternoon Shift (1pm - 9pm) = 16
Tuesday        =       Morning Shift (5am - 1pm) ; Afternoon Shift (1pm - 9pm) = 16
Wednesday   =      Morning Shift (5am - 1pm) ; Afternoon Shift (1pm - 9pm) = 16
Thursday       =      Morning Shift (5am - 1pm) ; Afternoon Shift (1pm - 9pm) = 16
Friday            =      Morning Shift (5am - 1pm) ; Afternoon Shift (1pm - 9pm) = 16
Saturday        =      Morning Shift (5am - 1pm) =  8
Sunday          =      0

 

On the photo I have create the following, separated time for Start time and end time as well as  day of week name and shifts (morning and night shift)

The expected output should be a morning shift total hours calculated in an 8 hour shift IN a 16 hour day shift and not in a 24 hour shift.

 

 

Sorry if it's a bit confusing.. I'm creating these graphs with only the Month/day (x-axis) and Active/Idle/off (%'s) but would like to calculate the within 16 hours instead of 24 hours.. 

I have split the shifts with this formula: 

Shift =

VAR startTime = Focus[Start Time]
VAR endTime = Focus[End Time]

return
Switch (TRUE(),
startTime >= TIME ( 5, 0, 0 ) && endTime<= TIME (13, 0, 0 ), "Morning Shift",
startTime >= TIME ( 13, 0, 0 ) && endTime <= TIME (21, 0, 0 ),"Afternoon Shift",
"Night Shift"
)

Morning and afternoon shift.PNG

 



fwhat ive done right now.PNG

@kasenotic Looks like an interesting problem and I love the production and supply chain stuff, but I am not going to type all that data. If you could paste a sample as text or provide a link to a PBIX that would be swell.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Hi @Greg_Deckler any luck?

@kasenotic See if the attached (below sig) file gives you any joy. Basically created a couple columns to calculate the total duration of each row in minutes and then the total duration in each shift in minutes.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors