Forum Discussion
Help Calculating YTD Utilization by Month
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!
Hi,
Try this measure
=CALCULATE([Billable hours],DATESYTD(Calendar[Date],"31/12")/(161.3333*MONTH(MAX(Calendar[Date])))
I have assumed the following:
- Billable hours is a measure
- 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
- There is an active relationship from the Date column of your Data Table to the Date column of the Calendar Table
- To your visual, you have dragged Year and Month from the Calendar Table
1 Reply
Hi,
Try this measure
=CALCULATE([Billable hours],DATESYTD(Calendar[Date],"31/12")/(161.3333*MONTH(MAX(Calendar[Date])))
I have assumed the following:
- Billable hours is a measure
- 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
- There is an active relationship from the Date column of your Data Table to the Date column of the Calendar Table
- To your visual, you have dragged Year and Month from the Calendar Table