Calculating Working Hours by Month

[micklowe]

Hi All

I want to work out an employees working hours by month for a KPI dashboard based on the following setup.

Employee table has 7 fields for the working hours per day:

Employee Name		Sun	Mon	Tues	Wed	Thu	Fri	Sat
John		0	7	7	7	7	7	0
Peter		0	5	5	5	5	0	0

I have a calendar table using NETWORKDAYS but can't see how to tie the 2 together.

Does anyone have any ideas please?

I'm trying to build a visual that shows hours logged vs actual hours:

Thanks

Mick

[StrategicSavvy · Accepted solution]

hi micklowe

Here are the steps to achieve this

1. Group your Employee table by days in PowerQuery (make sure each name of day is 3 letter abbreviation)

2. Add in your calendar table following columns:

I.Number of hours

Number of hours = 
VAR __NumberOfHoursLoggedInADay = 8 // number of hours you would like to log
VAR __NumberOfWorkingPeople = 2 // number of employees
RETURN
'Calendar'[Working Day] * __NumberOfHoursLoggedInADay * __NumberOfWorkingPeople

II. Day

Day = FORMAT([Date],"ddd")

3. Create relationship (one-to-many) between your calendar and working days aggregated data

4. Add new column in your calendar table

Logged Hours = RELATED('Working Hours'[Value])

5. Create 3 explicit measures

I. 

Total # of Hours = SUM('Calendar'[Number of hours])

II. 

Total # of Logged Hours = SUM('Calendar'[Logged Hours])

III,

KPI = DIVIDE([Total # of Logged Hours],[Total # of Hours])

6. Add Gauge chart to your report

I. Add Total # of Logged Hours to Values

II. Add Total # of Hours to Maximum

II. Add KPI to tooltips

Screen:

PBIX 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: www.linkedin.com/in/lukasz-kozdron 

[Greg_Deckler]

@micklowe Well they don't tie together because you don't have a Date in your first table nor do you have a WEEKDAY in your second table. So there's no relationship between them other than what you might invent in a measure. Also, you will likely want to unpivot your weekday columns in your first table otherwise this will be an absolute nightmare.