Forum Discussion

dsj8wksnnckk's avatar
dsj8wksnnckk
Resolver I
2 years ago
Solved

Work days calculation based on separation day

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.