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

[kasenotic]

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

[kasenotic]

@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??

 

[Greg_Deckler]

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

[kasenotic]

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

[kasenotic]

@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]

@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.

[kasenotic]

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"

)

f

[Greg_Deckler]

@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.

[kasenotic]

Hi @Greg_Deckler any luck?

[Greg_Deckler · 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.