PowerBI use of periods

[Anonymous]

Dear all,

I am a newbie in PowerBI and stuck on a challenge. 

I have two datasets about demand and capacity. 

Capacity Dataset has the teams, a period (StartDate, EndDate) and the number of people(FTE) we have during that period.

The Demand Dataset has the Activity, a period for implementation(StartDate, EndDate), the team needed and number of people(FTE) needed from the team.

I would like to create reports on these datasets 

• how many people we have each month from each team
• the demand requirements per month per team
• if the capacity is enough to meet the demand per month

I appreciate your help to solve this.

[Anonymous · Accepted solution]

Hi @Anonymous ，

Based on your description, you can do some steps as follows.

1. Create a calculated column in “Demand” table.

Resource Team =

LOOKUPVALUE (

    'Activity'[Resource Team],

    Activity[Resource Type], [Resourcetype]

)

2. Create two calculated tables.

Question 1 =

VAR x1 =

    GENERATE (

        GENERATESERIES ( 1, 12 ),

        VAR inc = [Value] RETURN ROW ( "date", DATE ( 2021, inc, 1 ) )

    )

RETURN

    FILTER (

        CROSSJOIN ( 'Activity', x1 ),

        [date] >= [StartDate]

            && [date] <= [EndDate]

    )




Question 2 =

VAR x1 =

    GENERATE (

        GENERATESERIES ( 1, 12 ),

        VAR inc = [Value] RETURN ROW ( "date", DATE ( 2021, inc, 1 ) )

    )

RETURN

    FILTER (

        CROSSJOIN ( 'Demand', x1 ),

        [date] >= [StartDate]

            && [date] <= [EndDate]

    )

3. Create a measure.

Measure =

var x1 =

SUMX (

    FILTER (

        ALL ( 'Question 1' ),

        [date] = SELECTEDVALUE ( 'Question 1'[date] )

            && [Resource Team] = SELECTEDVALUE ( 'Question 1'[Resource Team] )

    ),

    [FTE]

)

var x2 =

COUNTX (

    FILTER (

        ALL ( 'Question 2' ),

        [date] = SELECTEDVALUE ( 'Question 2'[date] )

            && [Resource Team] = SELECTEDVALUE ( 'Question 2'[Resource Team] )

    ),

    [Demand Title]

)

return

IF(x1>=x2,"Yes","No")

4. Create some matrices.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,

See if the HR blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Both tables need to be joined twice with date table