Determining Manufacturing Schedule Loaded per cell

[bkwohls]

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

[v-shex-msft]

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:

Before You Post, Read This 

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

[bkwohls]

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.

[v-shex-msft · Accepted solution]

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

[daxer-almighty]

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

[bkwohls]

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.