Date Calculation

[Airtrunk0801]

My scenario is I am trying to calcuted monthly man hours on our site. 

The data that I have is "date hired" of each employees, meaning I will have different Year and dates for each. I only want to calculate monthly hours. Some employees started early of the month, mid-week or last week of the month. how can i calculate that using powerbi? And I dont need to update the formula every month?

If i use excel I will use formula = Networkdays(startDate, EndDate), However with this formula I will need to change the startdate of the old employees  and end date every month. 

[Shravan133]

Create a Calendar Table

You'll need a calendar table to calculate working days in each month. You can create this using DAX:

Calendar = CALENDAR(MIN(Employee[Date Hired]), TODAY())

This will create a calendar starting from the earliest hire date up to the current date.

1. Calculate Monthly Man-Hours

You can use DAX to calculate the working days for each employee within the month they were hired and beyond.

Step 1: Define Workdays

First, you might want to define the number of workdays in a month using a DAX formula like this:

MonthlyWorkdays =

CALCULATE(

    COUNTROWS('Calendar'),

    DATESBETWEEN(

        'Calendar'[Date],

        MAX(Employee[Date Hired]),

        EOMONTH(TODAY(), 0)

    ),

    'Calendar'[IsWorkday] = TRUE()  // Assuming you have a column indicating workdays

)

Here, the formula calculates the working days for the employee from their hire date to the end of the current month.

Step 2: Calculate Man-Hours

Assuming an 8-hour workday, you can calculate the man-hours for each employee:

MonthlyManHours = [MonthlyWorkdays] * 8

This formula multiplies the number of working days by 8 (or whatever your standard working hours per day are).

1. Automating Monthly Updates

• The use of TODAY() in the formulas ensures that the calculations automatically update every month without needing manual adjustments. It will always calculate the man-hours for the current month based on the employee’s hire date and working days within that month.

1. Adding Constraints for Partial Month Employees

For employees who started partway through the month, the calculation will automatically handle them by counting workdays only from their hire date.

1. Summarizing the Results

To get the total monthly man-hours for all employees, you can create a measure that sums up the individual man-hours:

TotalMonthlyManHours = SUMX(Employee, [MonthlyManHours])

This will give you the total man-hours for the current month across all employees, accounting for their start dates.

Optional: Incorporate Holidays

If you want to exclude holidays, you can extend your calendar table to mark holidays and adjust the calculations to skip those days.