Help Calculating YTD Utilization by Month

[Anonymous]

I am trying to calculate YTD Utilization by month for my team. I have two different calculations that I am trying to do. The first is per team member. The second is for the entire team.

Each month the total available utilization hours are 161.3333. There is a table that houses all of the billable hours for each team member and I am already calculatin total billable hours with a measure in Power BI. Here is an example of what it looks like in Excel as well as the calculation for May...

This is what I have currently in PowerBI for a team member report. I would like to add the YTD Utilization to this chart and possible a guage that shows the current YTD.

Any help is greatly appreciated!

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=CALCULATE([Billable hours],DATESYTD(Calendar[Date],"31/12")/(161.3333*MONTH(MAX(Calendar[Date])))

I have assumed the following:

1. Billable hours is a measure
2. There is a Calendar Table with a column of Year and Month.  The Dates in the Calendar Table should run until the last date in the Date column of your Data Table
3. There is an active relationship from the Date column of your Data Table to the Date column of the Calendar Table
4. To your visual, you have dragged Year and Month from the Calendar Table