topic Re: % of Hours by Month in DAX Commands and Tips

% of Hours by Month

ctedesco3307 — Fri, 19 Nov 2021 21:55:26 GMT

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=111016731196150377040&rtpof=true&sd=true

Tempo Plans

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

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.

Re: % of Hours by Month

amitchandak — Sat, 20 Nov 2021 03:41:34 GMT

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

Re: % of Hours by Month

ctedesco3307 — Mon, 22 Nov 2021 22:11:18 GMT

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....

Re: % of Hours by Month

Anonymous — Wed, 24 Nov 2021 10:04:58 GMT

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

Re: % of Hours by Month

ctedesco3307 — Wed, 24 Nov 2021 13:19:13 GMT

Hi Anonymous - 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