Total working hours / week

Hi,

Thank you so much for your help. It is all working fine now.

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!

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

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! 

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.

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])

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.

Hi,

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

This is what I wanted you to have:

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.

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?

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?

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?

I understand! :-) 

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

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.

Hi tex628,

Just forgot about what I just said. It must be the return from holiday that is playing a trick on me.

It is fine. I have got the right results in both instance.

The only thing I can't figure out is that 5+hours being classified as 35hours +. It is one of those week that was falling into two periods.

Can you post the measure that you are currently using to calculate the hours? 

Hi,

It is really weird what is happening. When I click on the count of ID for more than 35hours, ID 7173 shows up under this bracket but only display 5:40 hours. However, from another table, if I click on the same driver, the table updates and shows the correct amount.

My formula is

DifferenceDur = SUM(StartStop[LastActionDec]) - SUM(StartStop[FirstActionDec])

Decimal Hours (Hrs) = ([DifferenceDur])/60

And for the grouping? :-)

Hi,

HOURLY BRACKETS = VAR DRIVER = DriverStartStop[Driver]
VAR WEEK = DriverStartStop[weeknum]
RETURN
IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)<20,"Less than 20hrs",IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)<=35,"Btw 20hrs & 35 hrs",IF(CALCULATE(SUM(DriverStartStop[Decimal Hours Driver]), ALL(DriverStartStop), DriverStartStop[Driver]=DRIVER, DriverStartStop[weeknum]=WEEK)>35,"More than 35hrs")))

No worries! :-)