Forum Discussion

corange's avatar
corange
Icon for Post Patron rankPost Patron
7 years ago
Solved

Total working hours / week

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 ...
Need Help
  • tex628's avatar
    tex628
    7 years ago

     

    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)

     

tex628's avatar
tex628
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
corange
Icon for Post Patron rankPost Patron
7 years ago

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's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      corange
      Icon for Post Patron rankPost Patron
      7 years ago

      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's avatar
        tex628
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Replace the ; in the end with ",

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