Total working hours / week

[corange]

Hi everyone,

I am new to POWER BI and I am struggling to get to where I need. I have a set of data with daily start and finish time / employee. I have calculated the duration in minutes and then converted it in decimal hours.

I need to show the weekly average hours and then group them into categories (less than 20 hours / week , between 20hrs and 35 hours / week, and more than 35hrs / week). I have looked in the forum to obtain the total hours / week, creating a week number column but I am lost and can't achieve what I am after.

Would you be able to help me.

Thanks in advance.

[tex628]

Hello, 

I would make a calculated column to classify each row. 

Column = 
VAR person = 'Table'[Person]
VAR week = 'Table'[Week]
return
IF(
Calculate( Sum( 'Table'[Hours] );
All( 'Table' );
'Table'[Person] = person;
'Table'[Week] = week) < 20 ; "Less than 20 Hours" ;

IF(
Calculate( Sum( 'Table'[Hours] );
All( 'Table' );
'Table'[Person] = person;
'Table'[Week] = week) <= 35 ; "Between 20 and 35 Hours" ; "More than 35 Hours" )

You might need to change your week column to a year/week column if you have dates that pass through more than one year.

[corange]

Hi tex628,

This didnt work for me. I will try to be more specific in my explanation.

I have got a list of employee ID with their daily starting and finish time. For each daily log, I did finish - start time using DATEDIFF to calculate duration in minutes. I have then use DATEDIFF / 60 to obtain Decimal Hour.

I now need to calculate the weekly ave working hours and group employees into categories <20hrs, 20hrs to 35hrs, More than 35 hrs. The graphs need to display weekly decimal hours with the legend 20hours etc... but we work by period. In a month, we have 2 periods (1st period runs from 1st to the 15, Period 2 runs from 16 to end of the month).

What other information would you require to be able to guide me here?

[tex628]

Your intensions are quite clear! 🙂 What was the result of the column i provided, did it not work at all? If possible, post a picture.

/ J

[corange]

Thanks for your reassurance. I am quite lost and I feel like I am overcomplicating things.

I have write your formula however, it didnt accept the ; so I tried with a , . It still didnt work. The attached is what I am getting.

[tex628]

Replace the ; in the end with ",

Like this:
"Less than 20 hours",
instead of:
"Less than 20 hours;

[corange]

Thnak you. I am still having an error showing up. Am I missing something?

[corange]

thank you so much. It worked in terms of categorising the data.

However, I have got a little issue with regards to how I am running the billing. As advised before, I have two periods, Period 1 runs from the 1st to the 15th and Period 2 runs from the 16th to end of month. I have use a formula to get the week number and now part of the week falls under P1 and the other part falls under P2 and the results don't look right.

Is there a way I could have a definite cut with regards to Periods while still having a weekly average?

[tex628]

Well if you want to make a definite cut you are by definition changing the dates of your P1 and P2. 
For example, including week 29 in P2 in the example below will mean that you have to extend the period for P2 to include an earlier date than the 16th. 

Generally speaking, as weeks will not occur in the same pattern as your periods i would recommend that you change the measurement of your average to use a sub-period instead. 

Maybe from your P1 and P2 in July you create:
July_P1_S1,
July_P1_S2,
July_P2_S1,
July_P2_S2

With this pattern you can create the same average, not on week but on this classification instead. 

I hope you understand what im getting at!

[corange]

Hi,

I am really new so I must admit that i am a bit lost. So instead of creating a colum with a week number, I go ahead and create the colum with the pattern July_P1_S1, July_P1_S2 for each month and period and use that column in the formula you gave me for the VAR WEEK instead?

How would you go about getting this pattern added automatically to the table? '

Thank you

[tex628]

I would create a calculated column in the calendar table, something similar to this:

Column =
'Calendar'[Month] & "_" & 
IF(DAY('Calendar'[Date])<8 , "P1_S1" , 
IF(DAY('Calendar'[Date])<16 , "P1_S2" , 
IF(DAY('Calendar'[Date])<24 , "P2_S1" , 
IF(DAY('Calendar'[Date])<31 , "P2_S2"
))))

This should give you a column that you can use instead of the weekcolumn, try it and see if the result is suitable! 

[corange]

Hi,

Thank you for your help.

I have tried this but it gives me daily hours classifying everything into the <20 hours bracket. The Calendar setting i have used is the date the employees have logged in.

[tex628]

On the measure, replace the first instance of "DriverStartStop[DriveDate]" with a month column!

If you dont have a month column, you can easily make one with a calculated column:

Column = MONTH('DriverStartStop'[DriveDate])

[corange]

Hi,

Thanks for your reply. I have tried that and it actually gave me the exact same results than what we had with the first formula. Week 29 display the results the same way. I have realised thought that they are split into the right period and adding up to a category. For example on week 29, I have 5:40 for one employee and it classify the employee in more than 35hrs for the week. which adding up with week 28 is correct. It didnt change anything.

[corange]

Hi,

Just following up on my last message. I have tried again but it doesn't give me the right results.

[tex628]

This is what I wanted you to have:

Month	July
Period	July_P1_S1	July_P1_S2	July_P2_S1	July_P2_S2
ID1	21	12	22	21
ID2	22	31	21	15
ID3	31	41	41	42
ID4	12	21	23	12
ID5	41	11	41	42

You cant combine weeks with your periods, you need to use a custom subperiod and not use week at all if you want this to work.

[corange]

Hi tex628,

I have created a month column as advised and then replace drivedate with that month column as suggested but it didn't change anything for me. It is grouped the same way. I do obtained the table the way you have presented in your last answer. However, I have a number in front instead of simply having P1_S1 etc... and one display the month number and doesnt group it into the categories P1_S1 etc... as intended. We obtain the same results than before.

I have missed something?

[tex628]

The blank ones are my misstake, 

Column =
'Calendar'[Month] & "_" & 
IF(DAY('Calendar'[Date])<8 , "P1_S1" , 
IF(DAY('Calendar'[Date])<16 , "P1_S2" , 
IF(DAY('Calendar'[Date])<24 , "P2_S1" , 
IF(DAY('Calendar'[Date])<=31 , "P2_S2"
))))

I missed a "=" on the last IF statement. 

But if we take the first week (P1_S1) in July, what is the desired result?

[corange]

Hi,

I have just sent the email and figued it out after clicking sent. I have modified my formula and now it does include the dates bracket correctly. So for each P1_S1 & P1_S2 i have the correct number of hours for the bracket. However, i do not wish to present the results that way. I need the total of the period.

The issue with the first option we worked with was that it was splitting one day (etc... 29 e.g.) and then including part of the results in to P1 the other into P2. Now, we have the correct split but I can't display the results as the total of the period. Do I make sense?

[tex628]

I understand! 🙂 

I'm not quite sure if this works but check if you can toggle subtotals on column level, 

[corange]

Hi tex628,

I am really sorry but I keep on facing little issues with this one.

i have group the row to obtain total and I am wondering if I could only display the total and have something say P1 with the month above.

i have this option avaible and I thought the results was not matching the results of our new formua P1_S1 etc... Turns out that when I added the total with that new formula, the system also shows a total of 6 while the distinct count for the table shows 7 as well as the ID details.