NOTE2: Month-wise table will result in huge data, therefore i have clubbed the hours consumed year-wise.
For year 2016, Burt has worked only for 7 months. If I click on his name I get answer correctly since count(distinct) eliminates all duplicates and choose his value as 1.
Problem appears when I employ the same formula for the complete team in the year 2016 and 2015. In reference with the above table we see that Burt has left the company in July 2016 which results in 3.6 team members for the year 2016. Similarly for the year 2015 when John left the company team members for the year 2015 is 3.54.
Count(Distinct) works well if the team members are available throughout the year, but my pain point is, what happens when one of employee leaves the company in between the year?