Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
May need your help in to solve an issue that i currently face . I am currently stuck and no idea if this is possible to do in PowerBI (currently doing it in excel and would like to automate the report ). I have a case where we need to monitor the loading for each machine that are assigned . So each job number will be having balance hours and days remaining for it to complete the job. I have created a calendar table that are connected to the job operation date. My main question is how do i generate hours (balancehours / days ) to get an output similar as below .
(above is the expected out but in excel .. the chart is based on week , i would like by date so that I can monitor by weekly , month and year)
I have few conditions also:
-The hours divided must be within job start date (JobOper_StartDate) and end date (JobDue_Duedate) .
-Job that are passed due date not need to generate and will go to backlog hours as shown in chart
So the expected output : We are able to sum the total hours divided based on date , month and also based on machine(resource) .
Here are sample data for factory loading :
Calculated_BalanceHrs | Days | Job Num | JobOper_Startdate | JobOper_DueDate | Resource |
34.71 | 2 | 172008 | 28-Jun-23 | 27-Jun-23 | CM4 |
20.93 | 4 | 172631 | 18-Sep-23 | 15-Sep-23 | CM4 |
24.48 | 3 | 172632 | 07-Jul-23 | 05-Jul-23 | CM3 |
32.78 | 3 | 172637 | 23-Jun-23 | 21-Jun-23 | CM2 |
14.58 | 2 | 172684 | 14-Jul-23 | 13-Jul-23 | CM2 |
6.38 | 2 | 172687 | 13-Jul-23 | 12-Jul-23 | CM2 |
10.04 | 2 | 172694 | 07-Jul-23 | 06-Jul-23 | CM2 |
35.18 | 6 | 172720 | 27-Jun-23 | 22-Jun-23 | CM3 |
12.85 | 2 | 172809 | 16-Jun-23 | 15-Jun-23 | CM4 |
6.79 | 2 | 172818 | 27-Jun-23 | 26-Jun-23 | CM4 |
71.27 | 4 | 165294 | 24-Nov-23 | 21-Nov-23 | CM1 |
36.76 | 5 | 168177 | 22-Aug-23 | 18-Aug-23 | CM1 |
34.48 | 6 | 170292 | 06-Dec-23 | 01-Dec-23 | CM1 |
34.48 | 2 | 170293 | 03-Nov-23 | 02-Nov-23 | CM1 |
34.48 | 6 | 170294 | 04-Oct-23 | 29-Sep-23 | CM1 |
34.48 | 6 | 170295 | 06-Sep-23 | 01-Sep-23 | CM1 |
34.48 | 2 | 170296 | 04-Aug-23 | 03-Aug-23 | CM1 |
14.7 | 5 | 170300 | 28-Nov-23 | 24-Nov-23 | CM1 |
14.7 | 5 | 170302 | 26-Sep-23 | 22-Sep-23 | CM1 |
14.7 | 5 | 170303 | 29-Aug-23 | 25-Aug-23 | CM1 |
14.7 | 2 | 170304 | 28-Jul-23 | 27-Jul-23 | CM1 |
14.7 | 5 | 170305 | 27-Jun-23 | 23-Jun-23 | CM1 |
67.74 | 8 | 171880 | 18-Aug-23 | 11-Aug-23 | CM2 |
130.44 | 15 | 171881 | 05-Oct-23 | 21-Sep-23 | CM1 |
21.15 | 2 | 171883 | 28-Jul-23 | 27-Jul-23 | CM1 |
40.65 | 7 | 171884 | 26-Sep-23 | 20-Sep-23 | CM1 |
43.56 | 4 | 171885 | 30-Jun-23 | 27-Jun-23 | CM4 |
91.16 | 10 | 171886 | 05-Oct-23 | 26-Sep-23 | CM1 |
19.72 | 2 | 172139 | 03-Aug-23 | 02-Aug-23 | CM4 |
19.72 | 2 | 172140 | 06-Jul-23 | 05-Jul-23 | CM4 |
14.3 | 4 | 172143 | 10-Jul-23 | 07-Jul-23 | CM4 |
1.8 | 2 | 172454 | 29-Jun-23 | 28-Jun-23 | CM2 |
21.45 | 2 | 172524 | 19-Jul-23 | 18-Jul-23 | CM1 |
24.84 | 2 | 172623 | 04-Jul-23 | 03-Jul-23 | CM4 |
8.36 | 2 | 172627 | 13-Sep-23 | 12-Sep-23 | CM4 |
8.36 | 2 | 172628 | 05-Jul-23 | 04-Jul-23 | CM2 |
19.96 | 2 | 172629 | 14-Sep-23 | 13-Sep-23 | CM4 |
19.96 | 2 | 172630 | 05-Jul-23 | 04-Jul-23 | CM3 |
45.3 | 4 | 172681 | 14-Jul-23 | 11-Jul-23 | CM3 |
4.39 | 4 | 172685 | 19-Jun-23 | 16-Jun-23 | CM4 |
25.4 | 6 | 172711 | 04-Jul-23 | 29-Jun-23 | CM3 |
22.95 | 3 | 172712 | 29-Jun-23 | 27-Jun-23 | CM3 |
44.31 | 7 | 172713 | 21-Jun-23 | 15-Jun-23 | CM2 |
3.55 | 1 | 166957 | 21-Jun-23 | 21-Jun-23 | CM2 |
6.56 | 0 | 171533 | 16-May-23 | 16-May-23 | CM1 |
4.48 | 1 | 172088 | 23-Jun-23 | 23-Jun-23 | CM2 |
4.49 | 1 | 168032 | 29-Aug-23 | 29-Aug-23 | CM1 |
Thanks in Advance.
Solved! Go to Solution.
Thanks for the input but what i am missing was another step .Issue with above it will not show the exact divided hours by day. The solution that I found is I use List and I generate row using Power Query .This will generate row based on start and due date .
List.Select(List.Dates([JobOper_StartDate], Duration.Days([JobOper_DueDate] - [JobOper_StartDate]) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_) >= 0 and Date.DayOfWeek(_) <= 4)
(this list will also exclude weekend)
And after that I just divided by hours and it will show by day, month and week.
Hi @Anonymous ok, to be precise as much as possible, please provide sample output from your table above or you just need simple division for "calculated balancehours/days) "?
Proud to be a Super User!
Hi @some_bih, My goal is to divide the calculate balance hours from job start date and due date ( i get the days from deducting from these dates) and plot a chart based on date. If i do a simple divide i would not get the divided hours on the days between start date and due date.
@Anonymous to provide some possible solution please provide expected output from your sample data.
Proud to be a Super User!
hi @some_bih , i have updated the post with the expected output . Basically the table in excel (below the chart) is what i what i want the output . In the expected output is by week . But i want it by date(daily) so that I can plot a chart monthly.
Hi @Anonymous based on your sample data I created calculated column
Adjust Sheet 2 to your table name and created two simple measure
Proud to be a Super User!
Thanks for the input but what i am missing was another step .Issue with above it will not show the exact divided hours by day. The solution that I found is I use List and I generate row using Power Query .This will generate row based on start and due date .
List.Select(List.Dates([JobOper_StartDate], Duration.Days([JobOper_DueDate] - [JobOper_StartDate]) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_) >= 0 and Date.DayOfWeek(_) <= 4)
(this list will also exclude weekend)
And after that I just divided by hours and it will show by day, month and week.
Hi @Anonymous to calculate difference in HOURS, DAYS, or other time frame in DAX there is function DATEDIFF. Check link for example and choose your time frame. Hope this help
https://learn.microsoft.com/en-us/dax/datediff-function-dax?WT.mc_id=DP-MVP-4025372
Proud to be a Super User!
Hi @some_bih , what i want to produce is the divided hours(calculated balancehours/days) based on the job start date and due date and able to plot into bar chart based on dates and resource