Available time for each worker

Hi Anonymous 

I have taken a look at your case, and I think it is doable. 

1. Open power query, and sort the table by Worker ID, Deadline, Task ID and Days, in that order. It should look like this(notice the small numbers next to the sort symbol):

2.  add an index column in power query, rename it to e.g. index, close and apply your changes

3.  Create your [New Start day]-column like this:

new start day = 
VAR _worker = [Worker ID]
VAR _endDay = [endDay]
VAR _startDay = [startDay]
VAR _task = data[Task ID]
VAR _index = data[Index]
VAR _overlappingTasks =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( data, [Task ID], data[startDay], data[endDay], data[Index] ),
            "numberOfOverlappingDays", DATEDIFF ( data[startDay], _endDay, DAY )
        ),
        FILTER (
            ALL ( 'data' ),
            [Worker ID] = _worker
                && [startDay] < _endDay
                && _task <> [Task ID]
                && _index < data[Index]
        )
    )
VAR _numberOfOverlappingDays =
    SUMX ( _overlappingTasks, [numberOfOverlappingDays] )
RETURN
    data[startDay] - _numberOfOverlappingDays

4. In order to display which workers who are available for a new task, first create a table of dates like this

Dates = CALENDAR(DATE(2019,1,1),DATE(YEAR(TODAY())+1,6,30)) 

5. Then create a table with the number of days a duration can last(rename the column from [value] to [duration]):

Duration = GENERATESERIES(1,15)

6. Create a measure like this:

Available workers = 
VAR _start =
    CALCULATE ( MAX ( Dates[Date] ) )
VAR _duration =
    CALCULATE ( MAX ( 'Duration'[Task duration] ) )
VAR _end = _start + _duration
VAR _worker =
    CALCULATE ( SELECTEDVALUE ( data[Worker ID] ) )
RETURN
    COUNTROWS (
        FILTER (
            data,
            ( _start <= data[new start day]
                && _end > data[new start day] )
                || ( _start < data[new end day]
                && _end >= data[new end day] )
                || ( _start <= data[new start day]
                && _end >= data[new end day] )
        )
    ) + 0

6. Create a table in the report with the worker ids, and use [Available workers] as a filter for this table(in the filter pane of the visual). If  [Available workers] returns 0 then the worker is available

7. Add slicers to the report page for the Date and Duration(using the columns from the tables created in step 4 and 5.

I have attached the updated report

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.