Total working hours / week

Hi,

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

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! :-)