1 Reply Latest reply on Aug 5, 2016 10:26 AM by Alexander Mou

    Calculate number of days by year/quarter/month with Start and End Dates in different years

    Hang Dang


      I was hoping to get some help on solving a few issues with the attached database and Tableau calculations.


      Database description: the data consists of "level of effort" that we require for people in specific departments with: level of effort (values are assigned based on the level of effort), number of hours per day required (based on level of effort), Start date and End Date for when we require these resources for each project.


      Key questions I need to answer:

      1. How many hours in total is each of these people required by Year, By quarter, by month?

      2. What is the % of their total available time (based on 1 FTE available hours) that is tapped into? Basically to measure if they have the capacity to do all of this work or not.


      The issues I ran into:

      1. Calculation of "Duration" required for each person's involvement: I used Datediff function to calculate the days between Start Date and End Date. However, it does not allow me to measure the Duration by year, quarter, month. For example, person ID=1 has a listed Start Date = March 1, 2014 and End Date = March 1, 2017. The total days of involvement = 1,278 days. However, I want to see how many days in 2017 he/she will have to work on this project, and my current calculation cannot accommodate it.


      2. Calculation of "Total Number of Hours": because #1 above does not work properly within a defined year, the number of hours (= duration in days *  number of hours per day) is overestimated for the same person.


      3. What would be the best way to answer question #2 (% of total available hour based on 1 FTE hours)?


      Thank you for your recommendations/help!