How to generate hours that are allocated based on date and machine

[Anonymous]

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.

[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) "?

[Anonymous]

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.

[Anonymous]

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.

[some_bih]

Hi @Anonymous based on your sample data I created calculated column 

Min_Date = MIN(Sheet2[JobOper_Startdate],Sheet2[JobOper_DueDate])

This column is date key column fot Date table. Date table for week overiew is crated by solution by SQLBI on link https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/ . With Date table you can see data by week and by month as shown on picture below. 

Adjust Sheet 2 to your table name and created two simple measure 

Sum hours = SUM(Sheet2[Calculated_BalanceHrs])

Sum backlog = SUM(Sheet2[Backlog])

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous · Accepted 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.

[some_bih]

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 

[Anonymous]

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