Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
@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??
@kasenotic Looks like it did a change type to Duration. I wouldn't, I would leave it is Text.
Alright, I'll work with this way instead, thank you for your help! 🙂
@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
@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.
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:
f
@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 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.