% of Hours by Month

[ctedesco3307]

Hi All! This report shows how many hours a person is 'booked' (plan time) to a project for a month. I want the values to show as a percentage based on how many working days there are in the month. If there were 160 working hours an the person was booked for 80 the value would show on the report as 50% 

Calendar Table 

https://docs.google.com/spreadsheets/d/1Quq6mlax15D2KXwQpYQl0KzDlzlGYNXD/edit?usp=sharing&ouid=11101...

Tempo Plans 

https://docs.google.com/spreadsheets/d/15oGfbSZlUPqlsR5i4LklkoNyQfDx-MVf/edit?usp=sharing&ouid=11101...

I have a pre-calculated planned hours measure that calculates the planned hours per day based on the start and end date of the plan 

Planned Hours 

https://docs.google.com/document/d/14HWaDlgUfo8nCABFlFfKyQBvsa9cfpN9a1qT8RO8HbQ/edit?usp=sharing

Many thansk in advance. 

[v-jayw-msft]

Hi @ctedesco3307 ,

Can you describe the meaning of some columns in your calendar table, like [THoliday], [Tworkday] and [WorkingDay].

And what is the meaning of 12/31/1899 in the [WorkingDay], does it mean it's not working day?

Best Regards,

Jay

[ctedesco3307]

Hi @v-jayw-msft - thank you for responding.

THoliday are company holidays, non working days. 

TWorkday is an identifier to tag if a day is a working day or not 

12/31/1899 is a placeholder for a non working day - This is the calculation for the column 

WorkingDay = IF(OR(TDate[WeekDay]=1,TDate[WeekDay]=7),date(1900,1,1),IF(ISBLANK(TDate[THoliday]),TDate[Date],date(1900,1,1)))

Pleae let me know if you need more info! Thank you 

[amitchandak]

@ctedesco3307 , If you have working hours, Please try a measure like

divide(sum(Table[Working Hours]), Sumx(Summarize(Table, Table[Full Name], Table[Month Year], "_1", 160), [_1]))

The denominator should have something from fact table, if full name is from dimension then use employee id from fact

[ctedesco3307]

Thank you - @amitchandak  --  Is there a way to dynamically calculate the working days per month? Instead of harad coding the 160, I want to calculate the hours compared to the working days by month , or week, I'd like it to change based on the Calendar view I have, whether it's months or weeks....