Forum Discussion
Determining Manufacturing Schedule Loaded per cell
I have a very simple Data Source that tells me the Job, Start Date, Cell it will be manufactured on, and the estimated hours. I need to aggregate this Data into a simple calendar of Total hours by cell by Day. Is this possible? Thanks in advance
Jobs Table
| Job Number | Cell | Part # | JobStartDate | EstHours |
| 1234a | 1 | x123 | 9/3/2021 | 17.5 |
| 998b | 1 | x557 | 9/3/2021 | 23 |
| 887c | 2 | 3wec | 9/1/2021 | 7 |
| 665d | 2 | 3det | 9/2/2021 | 11 |
I am guessing this would be the first intermediate Step??
| Job Number | Cell | 9/1/2021 | 9/2/2021 | 9/3/2021 | 9/4/2021 | 9/5/2021 | 9/6/2021 | 9/7/2021 |
| 1234a | 1 | 8 | Skip WE | Skip WE | 8 | 1.5 | ||
| 998b | 1 | 8 | Skip WE | Skip WE | 8 | 7 | ||
| 887c | 2 | 7 | Skip WE | Skip WE | ||||
| 665d | 2 | 8 | 3 | Skip WE | Skip WE |
Final Desired Result:
| Cell | 9/1/2021 | 9/2/2021 | 9/3/2021 | 9/6/2021 | 9/7/2021 |
| 1 | 16 | 16 | 1.5 | ||
| 2 | 7 | 8 | 3 | 8 | 7 |
Hi bkwohls,
You can create a new table as calendar to store the date values based on the raw table, then write a measure to calculate datediff between the start date and the calendar date to get daily work hours.
After these steps, you can use the raw table category and new table date, measure to create the matrix visuals.
Sample formulas:
Calculated table.
Calendar = CALENDAR ( MINX ( 'Table', [JobStartDate] ), MAXX ( 'Table', [JobStartDate] ) + 365 )Measure:
Measure = VAR totalHour = CALCULATE ( SUM ( 'Table'[EstHours] ), ALLSELECTED ( 'Table' ), VALUES ( 'Table'[Job Number] ), VALUES ( 'Table'[Part #] ) ) VAR cDate = MAX ( 'Calendar'[Date] ) VAR jobStart = CALCULATE ( MAX ( 'Table'[JobStartDate] ), ALLSELECTED ( 'Table' ), VALUES ( 'Table'[Job Number] ), VALUES ( 'Table'[Part #] ) ) RETURN IF ( HASONEVALUE ( 'Table'[Job Number] ), IF ( cDate >= jobStart && WEEKDAY ( cDate, 2 ) <= 5, VAR diff = totalHour - ( COUNTROWS ( FILTER ( CALENDAR ( jobStart, cDate ), WEEKDAY ( [Date], 2 ) <= 5 ) ) - 1 ) * 8 RETURN IF ( diff >= 0, MIN ( MAX ( diff, 0 ), 8 ) ) ) )Result:
Regards,
Xiaoxin Sheng
5 Replies
Are you talking about a calculated table? A measure? Do you want to do this in Power Query? Or in PBI Desktop? Why are you pivoting data on date? This is not a format suitable for PBI modeling. Any particular reasons? Nothing is really clear about what you're trying to achieve...
How to Get Your Question Answered Quickly - Microsoft Power BI Community
My data source for Jobs data only provides me with a Start Date and a Estimate number of hours to complete the Job. Based on an 8 hour day and skipping weekends I want to be able to calculate how amny hours per day I have in teh Job Schedule.
Hi bkwohls,
It seems like a common analysis requirement about two date fields, you can take a look at the following link start/end dagte part if help:
In addition, you can also try to create a new table to expand and the date ranges and link to raw table records then you can simply aggregate these records from the date ranges:
Solved: Spread revenue across period based on start and en... - Microsoft Power BI Community
Regards,
Xiaoxin Sheng
I only have a start date and then the hours that are estimated. I am essentially trying to calculate an end date based on an 8 hour work day given the Start date and hours required. With that info then I can aggregate all the hours per day and evaluate resource load per day per manufacturing cell.
Hi bkwohls,
You can create a new table as calendar to store the date values based on the raw table, then write a measure to calculate datediff between the start date and the calendar date to get daily work hours.
After these steps, you can use the raw table category and new table date, measure to create the matrix visuals.
Sample formulas:
Calculated table.
Calendar = CALENDAR ( MINX ( 'Table', [JobStartDate] ), MAXX ( 'Table', [JobStartDate] ) + 365 )Measure:
Measure = VAR totalHour = CALCULATE ( SUM ( 'Table'[EstHours] ), ALLSELECTED ( 'Table' ), VALUES ( 'Table'[Job Number] ), VALUES ( 'Table'[Part #] ) ) VAR cDate = MAX ( 'Calendar'[Date] ) VAR jobStart = CALCULATE ( MAX ( 'Table'[JobStartDate] ), ALLSELECTED ( 'Table' ), VALUES ( 'Table'[Job Number] ), VALUES ( 'Table'[Part #] ) ) RETURN IF ( HASONEVALUE ( 'Table'[Job Number] ), IF ( cDate >= jobStart && WEEKDAY ( cDate, 2 ) <= 5, VAR diff = totalHour - ( COUNTROWS ( FILTER ( CALENDAR ( jobStart, cDate ), WEEKDAY ( [Date], 2 ) <= 5 ) ) - 1 ) * 8 RETURN IF ( diff >= 0, MIN ( MAX ( diff, 0 ), 8 ) ) ) )Result:
Regards,
Xiaoxin Sheng
