Forum Discussion

bkwohls's avatar
bkwohls
Helper I
5 years ago
Solved

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 ce...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

bkwohls's avatar
bkwohls
Helper I
5 years ago

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.

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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