cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

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

1 ACCEPTED SOLUTION
Community Support

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 3
Super User

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

Helper II

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

Community Support

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.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors