Forum Discussion
Total working hours / week
HOURLY BRACKETS = VAR DRIVER = DriverStartStop[Driver] VAR WEEK = DriverStartStop[OUR NEW CREATED SUB-PERIOD] 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")))Instead of referencing the week we now want to reference the new sub period that we are using instead (P1_S1 ... P1_S2 ... and so on)
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.
Replace the ; in the end with ",
Like this:
"Less than 20 hours",
instead of:
"Less than 20 hours;
Thnak you. I am still having an error showing up. Am I missing something?
You need to close the ALL() statements:
Like this:
ALL(DriverStartStop), DriverStartSt.....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?
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!