Splitting hours between working days

[Anonymous]

Hi

I've been sent on a task to make a capacity plan for the production.

I have a table with actual and budget hours for each production order. This yield a deviation, which are the the remaining hours for production. 

The number of remaining hours should then be split into pieces of max 7,4 hours until there is no remaining production orders.

Is this achieveable somehow?

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

One sample for your reference. Please check the following step as below.

1. Create a date table as below.

Table = var k =CALENDAR(DATE(2019,05,01),DATE(2019,05,31))
return
FILTER(k,WEEKDAY([Date],2)<=5)

2. Crossjoin the two tables.

Table 2 = var k =CROSSJOIN('Table',Table1)
return
ADDCOLUMNS(FILTER(k,[Date]>=Table1[Real date]),"value",0-[remaining])

3, Create a calculated column in table 2.

Hrs = 
VAR ms =
    CALCULATE (
        COUNT ( 'Table 2'[order no] ),
        FILTER (
            'Table 2',
            'Table 2'[order no] = EARLIER ( 'Table 2'[order no] )
                && 'Table 2'[Date] <= EARLIER ( 'Table 2'[Date] )
        )
    ) * 7.4
VAR i = ms - 'Table 2'[value]
VAR result =
    IF ( 'Table 2'[value] > ms, 7.4, 7.4 - i )
RETURN
    IF ( result > 0, result, BLANK () )

[Anonymous]

You're a genious btw. This is brilliant!