Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

workhours by month Help

Can someone help me with this isue i'm trying to create the workhours by month for every employee i do have their work hours by week for this is how it looks:

 

Employee                        Work hours by week

Bart                                             32

anna                                            40

john                                            40

danny                                          40

elizabeth                                     32

 

my data model contains a calender table. does anyone know how do i get their workhours over a month

 

many thanks for the help

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    As far as I know, it is hard for us to calculate the hours by month by hours by week, due to the possibility of the same week being divided into two months at the end of the month. One part in the end of current month and another part in the begin of next month. So I suggest you to convert hours by week into hours by day first and then multiply with count the working days per month to achieve your goal.

    Data model:

    Here I divide hours by week by 5. You can create a calculated column as below.

    hours by month = 
VAR _WorkingDay_PerMonth =
    CALCULATE (
        NETWORKDAYS ( MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ), 1 )
    )
VAR _hoursbyday = 'Table'[hours by week] / 5
RETURN
    _WorkingDay_PerMonth * _hoursbyday

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Anonymous 

    do you have other column relevant to date or week number?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      i only have another table where the workhours by week are written by the month for exmaple

       

      employee           hours by week               month

      bart                             32                               januari

      bart                             32                              januari

      bart                             32                             februari

      etc                               etc                            etc

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

         

        As far as I know, it is hard for us to calculate the hours by month by hours by week, due to the possibility of the same week being divided into two months at the end of the month. One part in the end of current month and another part in the begin of next month. So I suggest you to convert hours by week into hours by day first and then multiply with count the working days per month to achieve your goal.

        Data model:

        Here I divide hours by week by 5. You can create a calculated column as below.

        hours by month = 
VAR _WorkingDay_PerMonth =
    CALCULATE (
        NETWORKDAYS ( MIN ( 'Calendar'[Date] ), MAX ( 'Calendar'[Date] ), 1 )
    )
VAR _hoursbyday = 'Table'[hours by week] / 5
RETURN
    _WorkingDay_PerMonth * _hoursbyday

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.