Forum Discussion
How to generate hours that are allocated based on date and machine
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.
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.
8 Replies
- some_bih
Community Champion
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
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
- some_bih
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) "?
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.
- some_bih
Anonymous to provide some possible solution please provide expected output from your sample data.
