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...
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.

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.