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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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'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