Solved: Creating a date spread table for activities and re...

spuff55 · ‎02-12-2021

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 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

v-cazheng-msft · ‎02-22-2021

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

View solution in original post

spuff55 · ‎02-25-2021

That works great, thank you!

View solution in original post

v-cazheng-msft · ‎02-18-2021

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 · ‎02-18-2021

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 · ‎02-22-2021

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 · ‎02-25-2021

That works great, thank you!

amitchandak · ‎02-12-2021

@spuff55 , refer if these two blogs can help

https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

spuff55 · ‎02-15-2021

HI @amitchandak

Thanks for the examples, it's a great post but not sure if it solves the problem exactly.

Creating a date spread table for activities and resource

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly