workhours by month Help

[Krijgerss21]

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

[FreemanZ]

hi @Krijgerss21 

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

[Krijgerss21]

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

[v-rzhou-msft · Accepted solution]

Hi @Krijgerss21 ,

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.