Working Hours Formula Not Working

rosemilo , that is one hell of a complicated measure! I have taken the liberty of re-writing it in a way that I can understand. Here is my attempt which is hopefully easier to understand.

Working Hours = 
    // Get the CreatedDate as just YYYYMMDD
    VAR vCreatedDate =
        DATE(
            YEAR('Table'[CreatedDate]),
            MONTH('Table'[CreatedDate]),
            DAY('Table'[CreatedDate])
        )

    // Get the ClosedDate as just YYYYMMDD
    VAR vClosedDate =
        DATE(
            YEAR('Table'[ClosedDate]),
            MONTH('Table'[ClosedDate]),
            DAY('Table'[ClosedDate])
        )

    RETURN
        IF(NOT ISBLANK('Table'[CreatedDate]) && NOT(ISBLANK('Table'[ClosedDate])),
            // Get all MNL Working Days between CreatedDate and ClosedDate
            SUMX(
                FILTER(
                    'Date',
                    'Date'[Date] >= vCreatedDate
                    && 'Date'[Date] <= vClosedDate
                    && 'Date'[WorkDay MNL] = TRUE()
                ),

                // Get the working start time for this day and region
                VAR vStartTimeWorkingHours = 
                    SWITCH('Table'[Region],
                        "AMERICAS", 'Date'[Date] + TIME(15, 0, 0),
                        "APAC", 'Date'[Date] + TIME(9, 0, 0)
                    )

                // Get the working end  time for this day and region
                VAR vEndTimeWorkingHours =
                    SWITCH('Table'[Region],
                        "AMERICAS", 'Date'[Date] + TIME(23, 59, 59),
                        "APAC", 'Date'[Date] + TIME(18, 0, 0)
                    )

                // Work out the appropriate StartTime for this day
                VAR vStartTimeToUse =
                    IF(
                        vStartTimeWorkingHours < 'Table'[CreatedDate],
                        'Table'[CreatedDate],
                        vStartTimeWorkingHours
                    )

                // Work out the appropriate EndTime for this day
                VAR vEndTimeToUse =
                    IF(
                        vEndTimeWorkingHours > 'Table'[ClosedDate],
                        'Table'[ClosedDate],
                        vEndTimeWorkingHours
                    )
                
                // Get the duration in minutes (only when EndTime > StartTime)
                VAR vWorkingMinutesDuration = 
                    IF(
                        vEndTimeToUse > vStartTimeToUse,
                        DATEDIFF(vStartTimeToUse, vEndTimeToUse, MINUTE)
                    )

                // Return the duration in hours
                RETURN vWorkingMinutesDuration / 60.0 
            )
        )

What it does is:

- For each row in 'Table', loop through rows in 'Date' table that are between CreatedDate and ClosedDate, and that have [Workday MNL] = TRUE

- For each day, work out the start and end working times, taking into account the region

- Get the minutes between the start and end working times

- Divide that by 60 to get working hours

- SUMX the working hours

It seems to give the correct results, but unfortunately is seems to be quite slow. With your sample PBIX file, it takes about 2 minutes to calculate the new column. I leave it as an excercise for others to improve the speed!

And I'm not sure how the table 'Holidays and Weekends' fits into the calculation, as your measure does not seem to refer to that table at all.

Anonymous 
Looks better now