Work days calculation based on separation day

[dsj8wksnnckk]

Hi all,

I have 2 tables:
1. Table with Names and Separation date of employees 
2. Date table created with PQ, including a calculated column to show if the day is a work day (1 for Yes, 0 for No) and calculated column that show the work hours on that day (1 x 8h for work days, or 0 x 8h for non-working days)


I need a formula to calculate the number of work hours per employee, and I need it to be filterable by month-year slicer. (e.g I need the work hours for January 2024 for every employee separated)

I am convinced that this has a fairly clean solution but I am stuck at the moment.

Thanks in advance

[v-jialongy-msft · Accepted solution]

Hi @dsj8wksnnckk 

Try the following steps

Create a Relationship: Ensure there is a relationship between your employee table and the date table based on the date fields.

Create a Measure: Use DAX to create a measure that calculates the total work hours. This measure will sum the work hours from the date table for the dates that fall within each employee's active period up to their separation date.

Example DAX formula:

Total Work Hours = 
CALCULATE(
    SUM(DateTable[WorkHours]),
    FILTER(
        DateTable,
        DateTable[Date] <= MAX(EmployeeTable[SeparationDate]) &&
        DateTable[IsWorkDay] = 1
    )
)

Use a Month-Year Slicer: To make the measure filterable by month and year, ensure your date table has a column formatted as "Month-Year" or create one. You can then use this column to create a slicer in your Power BI report.

Best Regards,

Jayleny

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

[dsj8wksnnckk]

Thank you! This is it. 

[amitchandak]

@dsj8wksnnckk , You have networkdays in DAX , you can use that

Calculating Business Days with and without NETWORKDAYS DAX Function | 2023 Guide: https://www.youtube.com/watch?v=Qs03ZZXXE_c

For business Hours

https://exceleratorbi.com.au/calculating-business-hours-using-dax/