Forecast of Monthly FTE

[cmontoure22]

Output:

• Given the information in the attached spreadsheet I’m looking to create a bar chart that would show the # of FTE per month.

Path to get there:

• From Project tab
• Connect Type and Sizing Column to the Sizing Tab > Hours column for each Project.
• Connect Factory Code to Factory tab > Factory Name
• Create:
  • Net Working Hours per Month
    • Exclude Saturday and Sunday
    • Exclude days in the Holidays tab
    • Using Project tab > Start Date and End Date, distribute the Hours to each month
  • FTE
    • FTE = Networking Hours per Month / 8 hours per working day

Getting to the Net Working Hours per month is what’s really giving me issues. I can seem to find some of the components Net Working Hours per Month and the Net Working hours between the Start and End Dates, but can’t seem to pull it together.

I haven't even gotten to the FTE yet

Any thoughts?

Project Forecast FTE.xlsx 

[amitchandak]

@cmontoure22 , for businss hours you can refer blog from Matt

https://exceleratorbi.com.au/calculating-business-hours-using-dax/

[cmontoure22]

Thanks for this. Looking at the provided link, I understand it provides solution for how many hours worked between two Date/Times.

I'm looking something a bit different, how would I apply that to this?

Do you have a recomendation for knowing the Total Hours, how would I distribute those between two Dates.

Example: Assuming mutliple projects with different dates and hours. Ex 1

Project	Start Date	End Date	Hours
Project 1	4/1/2023	12/8/2023	1500