cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iQgQ
New Member

Resource Forecasting per month by using Start Date and End Date only

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 

1 ACCEPTED 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])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Sounds like Open Tickets:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!