Forum Discussion

micklowe's avatar
micklowe
Helper I
2 years ago
Solved

Calculating Working Hours by Month

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 SunMonTuesWedThuFriSat
John 0777770
Peter 0555500
         

 

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

 

Need Help
  • StrategicSavvy's avatar
    StrategicSavvy
    2 years ago

    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 

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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.

  • StrategicSavvy's avatar
    StrategicSavvy
    Resolver II
    2 years ago

    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