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.
Help please!
I have a resource forecast spreadsheet which highlights resources needed for a given activity/task with the following columns (among others of course); Start Date, End Date, Monthly Allocation (FTE).
I'm trying to count how many resources are forecasted for any given month (in a stacked column chart), but don't know how to do this. I've looked at other posts but can't seem to do this 'simple' task. please help! i have to create a bunch of different views by project and/or by department, by morning of April 6th. 😬
Many thanks for your help community!!! I've embedded my power bi file!
https://1drv.ms/u/s!AlbpYuNhyFaPg8tDP8XyPCPfMl1R7A?e=Qdqf6E
Solved! Go to Solution.
See attached below. I just implemented Open Tickets and just tweaked it so that it uses SUMX instead of COUNT/COUNTROWS. No idea if it is correct.
Tickets Open =
VAR tmpTickets = ADDCOLUMNS('Table1',"Effective Date",IF(ISBLANK([End Date]),TODAY(),[End Date]))
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpTickets,
'Date'
),
[Date] >= [Start Date] &&
[Date] <= [Effective Date]
),
"ID",[Resource Name],
"Date",[Date],
"Allocation",[Allocation (FTE)]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Allocation",SUMX(CURRENTGROUP(),[Allocation]))
RETURN SUMX(tmpTable1,[Allocation])
Sounds like Open Tickets:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364
thanks @Greg_Deckler - it's similar but not really. different logic - i need to sum the values within the 'allocation' column and not the rows. also i need to count any value as long as it is forecasted within the 'start date' and 'end date' for the particular month visualized.
in simple english, i need a stacked bar (which will filter by project or department) and shows the total FTEs per month 'allocated' as long as they were forecasted within that date range (i.e. start date and end date). i just started using Power BI yesterday and don't have any expereince in the tool at all and neither am a programmer. so really hoping someone can provide the code to include the caluclation - i made an attempt by looking at some topics/answers but have spent countless hours tryign to crack this.
help!
See attached below. I just implemented Open Tickets and just tweaked it so that it uses SUMX instead of COUNT/COUNTROWS. No idea if it is correct.
Tickets Open =
VAR tmpTickets = ADDCOLUMNS('Table1',"Effective Date",IF(ISBLANK([End Date]),TODAY(),[End Date]))
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpTickets,
'Date'
),
[Date] >= [Start Date] &&
[Date] <= [Effective Date]
),
"ID",[Resource Name],
"Date",[Date],
"Allocation",[Allocation (FTE)]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Allocation",SUMX(CURRENTGROUP(),[Allocation]))
RETURN SUMX(tmpTable1,[Allocation])
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |