Work days calculation based on separation day

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.