Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
I appreciate your help to solve this.
Solved! Go to Solution.
Hi @Anonymous ,
Based on your description, you can do some steps as follows.
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.
Hi @Anonymous ,
Based on your description, you can do some steps as follows.
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.
@Anonymous ,
See if the HR blog can help
Both tables need to be joined twice with date table
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
79 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |