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 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] / num
The result looks like this:
Here is the pbix.
Best Regards,
Caiyun Zheng
That works great, thank you!