Hi I've been trying to figure this out for too long and need some help. I have a database containing activity data with multiple resources assigned per activity, Each row contains Activity ID, Resource Name, Start, Finish, Budget Hrs. I'm trying to plot the resource over time and want to create a calendar table holding the value per activity/resource of each period throughout the duration of the activities. The source data looks like this:Activity IDResourceStartFinishBudgetACT001Jane01-Jan-2128-Feb-21100ACT001Dave01-Jan-2131-Mar-21100ACT002Jane01-Mar-2131-Apr-2120ACT003Bob01-Feb-2131-Apr-2175 I started with a DAX formula that creates a min/max date spread showing the start of each month. SPREAD_DATES = FILTER (CALENDAR(MIN('RESOURCE'[start]),MAX('RESOURCE'[finish])),EOMONTH ( [Date],-1 )+1 = [Date] But what I need is a table that has repeat periods where activities/resource overlap. So the table should look like this: Period Start DateActivity IDResourceBudget01-Jan-21ACT001Jane5001-Jan-21ACT001Dave3301-Feb-21ACT001Jane5001-Feb-21ACT001Dave3301-Feb-21ACT003Bob2501-Mar-21ACT001Dave3301-Mar-21ACT002Jane1001-Mar-21ACT003Bob2501-Apr-21ACT002Jane1001-Apr-21ACT003Bob25 Any help or suggestions appreciated. Thanks in advance.Matt

Forum Discussion

Frequent Visitor

5 years ago

Solved

Creating a date spread table for activities and resource

I've been trying to figure this out for too long and need some help.

I have a database containing activity data with multiple resources assigned per activity, Each row contains Activity ID, Resource Name, Start, Finish, Budget Hrs.

I'm trying to plot the resource over time and want to create a calendar table holding the value per activity/resource of each period throughout the duration of the activities.

The source data looks like this:

Activity ID	Resource	Start	Finish	Budget
ACT001	Jane	01-Jan-21	28-Feb-21	100
ACT001	Dave	01-Jan-21	31-Mar-21	100
ACT002	Jane	01-Mar-21	31-Apr-21	20
ACT003	Bob	01-Feb-21	31-Apr-21	75

I started with a DAX formula that creates a min/max date spread showing the start of each month.

SPREAD_DATES = FILTER (
CALENDAR(MIN('RESOURCE'[start]),MAX('RESOURCE'[finish])),
EOMONTH ( [Date],-1 )+1 = [Date]

But what I need is a table that has repeat periods where activities/resource overlap.

So the table should look like this:

Period Start Date	Activity ID	Resource	Budget
01-Jan-21	ACT001	Jane	50
01-Jan-21	ACT001	Dave	33
01-Feb-21	ACT001	Jane	50
01-Feb-21	ACT001	Dave	33
01-Feb-21	ACT003	Bob	25
01-Mar-21	ACT001	Dave	33
01-Mar-21	ACT002	Jane	10
01-Mar-21	ACT003	Bob	25
01-Apr-21	ACT002	Jane	10
01-Apr-21	ACT003	Bob	25

Any help or suggestions appreciated.

Thanks in advance.

Matt

Need Help

v-cazheng-msft

5 years ago

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

spuff55
5 years ago
That works great, thank you!

6 Replies

v-cazheng-msft

Community Support

5 years ago

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.

spuff55
Frequent Visitor
5 years ago
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
- v-cazheng-msft
  Community Support
  5 years ago
  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

amitchandak
Super User
5 years ago
spuff55 , refer if these two blogs can help

https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-Convert-to/ba-p/1476400

https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290
- spuff55
  Frequent Visitor
  5 years ago
  HI amitchandak
  Thanks for the examples, it's a great post but not sure if it solves the problem exactly.

Forum Discussion

Creating a date spread table for activities and resource

6 Replies

Related content

Creating ICMs via Activator

Activator created ICM UI issue

Fabric pipeline activities web activity Oauth error

Activity Statistics

Less resource consumption