Forum Discussion
Creating a date spread table for activities and resource
Hi, spuff55
Sorry for late reply. You can create a Calculated table and a Calculated column to get the result you want.
1 Calculated table
Table = VAR cal = SELECTCOLUMNS ( FILTER ( CALENDAR ( MIN ( RESOURCE[Start] ), MAX ( RESOURCE[Finish] ) ), DAY ( [Date] ) = 1 ), "Period", [Date] ) VAR cross_t = FILTER ( CROSSJOIN ( RESOURCE, cal ), [Period] >= RESOURCE[Start] && [Period] <= RESOURCE[Finish] ) VAR sel_t = SELECTCOLUMNS ( cross_t, "Period Start Date", [Period], "ActivityID", [Activity ID], "Resource", [Recource], "Budget_overall", [Budget] ) RETURN sel_t 2 Calculated column Budget = VAR num = CALCULATE ( COUNT ( 'Table'[Budget_overall] ), ALLEXCEPT ( 'Table', 'Table'[ActivityID], 'Table'[Resource] ) ) RETURN 'Table'[Budget_overall] / numThe result looks like this:
Here is the pbix.
Best Regards,
Caiyun Zheng
That works great, thank you!
Hi, spuff55
You can create a Calculated table to get the result you want.
New_table =
VAR start1 =
CALCULATE (
SELECTEDVALUE ( RESOURCE[Start] ),
RESOURCE[Activity ID] = "ACT001",
RESOURCE[Recource] = "Dave"
)
VAR finish1 =
CALCULATE (
SELECTEDVALUE ( RESOURCE[Finish] ),
RESOURCE[Activity ID] = "ACT001",
RESOURCE[Recource] = "Dave"
)
VAR mon_inter1 =
MONTH ( finish1 ) - MONTH ( start1 ) + 1
VAR t1 =
SELECTCOLUMNS (
FILTER ( CALENDAR ( start1, finish1 ), DAY ( [Date] ) = 1 ),
"Period Start Date", [Date]
)
VAR tt1 =
ADDCOLUMNS (
t1,
"ActivityID", "ACT001",
"Resource", "Dave",
"Budget",
FORMAT (
DIVIDE (
CALCULATE (
SELECTEDVALUE ( RESOURCE[Budget] ),
RESOURCE[Activity ID] = "ACT001",
RESOURCE[Recource] = "Dave"
),
mon_inter1
),
"#"
)
)
VAR start2 =
CALCULATE (
SELECTEDVALUE ( RESOURCE[Start] ),
RESOURCE[Activity ID] = "ACT001",
RESOURCE[Recource] = "Jane"
)
VAR finish2 =
CALCULATE (
SELECTEDVALUE ( RESOURCE[Finish] ),
RESOURCE[Activity ID] = "ACT001",
RESOURCE[Recource] = "Jane"
)
VAR mon_inter2 =
MONTH ( finish2 ) - MONTH ( start2 ) + 1
VAR t2 =
SELECTCOLUMNS (
FILTER ( CALENDAR ( start2, finish2 ), DAY ( [Date] ) = 1 ),
"Period Start Date", [Date]
)
VAR tt2 =
ADDCOLUMNS (
t2,
"ActivityID", "ACT001",
"Resource", "Jane",
"Budget",
FORMAT (
DIVIDE (
CALCULATE (
SELECTEDVALUE ( RESOURCE[Budget] ),
RESOURCE[Activity ID] = "ACT001",
RESOURCE[Recource] = "Jane"
),
mon_inter2
),
"#"
)
)
VAR start3 =
CALCULATE (
SELECTEDVALUE ( RESOURCE[Start] ),
RESOURCE[Activity ID] = "ACT002",
RESOURCE[Recource] = "Jane"
)
VAR finish3 =
CALCULATE (
SELECTEDVALUE ( RESOURCE[Finish] ),
RESOURCE[Activity ID] = "ACT002",
RESOURCE[Recource] = "Jane"
)
VAR mon_inter3 =
MONTH ( finish3 ) - MONTH ( start3 ) + 1
VAR t3 =
SELECTCOLUMNS (
FILTER ( CALENDAR ( start3, finish3 ), DAY ( [Date]) = 1 ),
"Period Start Date", [Date]
)
VAR tt3 =
ADDCOLUMNS (
t3,
"ActivityID", "ACT002",
"Resource", "Jane",
"Budget",
FORMAT (
DIVIDE (
CALCULATE (
SELECTEDVALUE ( RESOURCE[Budget] ),
RESOURCE[Activity ID] = "ACT002",
RESOURCE[Recource] = "Jane"
),
mon_inter3
),
"#"
)
)
VAR start4 =
CALCULATE (
SELECTEDVALUE ( RESOURCE[Start] ),
RESOURCE[Activity ID] = "ACT003",
RESOURCE[Recource] = "Bob"
)
VAR finish4 =
CALCULATE (
SELECTEDVALUE ( RESOURCE[Finish] ),
RESOURCE[Activity ID] = "ACT003",
RESOURCE[Recource] = "Bob"
)
VAR mon_inter4 =
MONTH ( finish4 ) - MONTH ( start4 ) + 1
VAR t4 =
SELECTCOLUMNS (
FILTER ( CALENDAR ( start4, finish4 ), DAY ( [Date] ) = 1 ),
"Period Start Date", [Date]
)
VAR tt4 =
ADDCOLUMNS (
t4,
"ActivityID", "ACT003",
"Resource", "Bob",
"Budget",
FORMAT (
DIVIDE (
CALCULATE (
SELECTEDVALUE ( RESOURCE[Budget] ),
RESOURCE[Activity ID] = "ACT003",
RESOURCE[Recource] = "Bob"
),
mon_inter4
),
"#"
)
)
RETURN
UNION ( tt1, tt2, tt3, tt4 )
The result looks like this:
Here is the pbix file.
Best Regards,
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Caiyun
That's really great, but although it provides the end result, I didn't mention that the original table is being driven by a project management tool and is always changing as new resource assignement and tasks are added.
Would there be a way to do the same thing without explicitly listing the names within the DAX so that new additions to the list are accounted for in the second table.
e.g. I added "Danny" to the first table and his task isn't calculated in the second.
Thank you for taking the time to answer, and for your jedi level solution.
Matt
Hi, spuff55
Sorry for late reply. You can create a Calculated table and a Calculated column to get the result you want.
1 Calculated table
Table = VAR cal = SELECTCOLUMNS ( FILTER ( CALENDAR ( MIN ( RESOURCE[Start] ), MAX ( RESOURCE[Finish] ) ), DAY ( [Date] ) = 1 ), "Period", [Date] ) VAR cross_t = FILTER ( CROSSJOIN ( RESOURCE, cal ), [Period] >= RESOURCE[Start] && [Period] <= RESOURCE[Finish] ) VAR sel_t = SELECTCOLUMNS ( cross_t, "Period Start Date", [Period], "ActivityID", [Activity ID], "Resource", [Recource], "Budget_overall", [Budget] ) RETURN sel_t 2 Calculated column Budget = VAR num = CALCULATE ( COUNT ( 'Table'[Budget_overall] ), ALLEXCEPT ( 'Table', 'Table'[ActivityID], 'Table'[Resource] ) ) RETURN 'Table'[Budget_overall] / numThe result looks like this:
Here is the pbix.
Best Regards,
Caiyun Zheng
That works great, thank you!