I'm new to Power BI and am trying to work through somethig that seems simple but has me stumped.
I am trying to calcuate the utilization percentage by month by employee.
Utilization = Worked Hrs. / Available Hrs.
Available hours = 8* xx workdays in a month
I have created a field to assign 8 hours per work day and have tried a variety of ways to calculate a sum total of hours for each month. In the example below, I am trying to calculate the "Available Hours" column for each month. So far, when I calculate the hours I get 2088 for 2019, which is the total number of hours for the year. How do I get the Available hours to display as seen below?
The data comes from a time tracking system. We import the data to Power BI to provide tracking on employee utilization on internal and extenal projects. There are calculations done in the time tracking system prior to exporting this data to Power BI. Our model then leverages both calculations and measures to create various metrics.
The matrix in which I am working provides a view at the team, user(employee) and project view to show how many hours were worked by each employee on each project for each month. "Available Hours" is currently provided by the system of record. In our dates table, we have a column called "Is Work Day" that serves as a binary indicator of whether or not the date is a work day. On the table where the employee hours are calculated, we only have the first day of the month in the date field (because we are only breaking down the data monthly).
We are simplifying our utilization calculation and just want to use total work days * 8 hours per day. When I try to calculate this in Power BI right now I get "2088" for each month, which is the total number of work hours for the given year (my data set starts in 2018). I have also tried to use TOTALMTD but haven't been able to get to a correct answer.