How to create an effort calendar by resources from tasks with only start and end dates?

[gianfrancob]

Hello, 

I would like to create a "calendared" dataset showing the aggregate effort for each resource for each day. 

My model consists of:

1. Resource table: with Id and Name
2. Tasks table: with resource Id, task id, task start, task end, the effort required
3. Calendar table: which is the time horizon I would like to calculate the aggregation. 

The output should be a table where each row has: each resource and each day, calculate the sum of the effort.

Resource 1 | Day 1 | Effort:100

Resource 1 | Day 2 | Effort:50

Resource 1 | Day 3 | Effort:50

Resource 2 | Day 1 | Effort:100

Resource 2 | Day 2 | Effort:100

Resource 2 | Day 3 | Effort:25

The challenge I am facing is that the tasks have a start and end date and I am not sure how to build the filters to perform that computation (attached PowerBI model). 

Any advice on how to proceed would be greatly appreciated (link to .pbix model: CLICK HERE )

Thank you

[v-frfei-msft · Accepted solution]

Hi @gianfrancob ,

We can create a calculated table as below.

Table 3 = 
VAR k =
    ADDCOLUMNS (
        CROSSJOIN ( DISTINCT ( 'Table'[task_id] ), CALENDARAUTO () ),
        "id", 'Table'[task_id]
    )
VAR eff =
    ADDCOLUMNS (
        k,
        "a", CALCULATE (
            SUM ( Tasks[effort] ),
            FILTER (
                Tasks,
                'Tasks'[task_id] = [id]
                    && [Date] >= Tasks[start_date]
                    && [Date] <= Tasks[end_date]
            )
        )
    )
RETURN
    FILTER ( eff, [a] <> BLANK () && [a] >= 0 )

[gianfrancob]

Hi,

thank you for the response, it seems to be working on the test dataset - I will roll that out on the PROD one. 

It would be great if you could also explain a few things regarding the solution:

• You create a table called "Table" where you summarise the Tasks by End Date? Is that correct? what is the rationale?

Table = SUMMARIZECOLUMNS(Tasks[end_date],Tasks[start_date],Tasks[task_id],"value",SUM(Tasks[effort]))

• When you use CALENDARAUTO() in the cross-join the date range is implicit and is not clear which range the function uses? 

Thank you

Best,

Gian