Monhtly average activity challenge

Here's how you can break it down:

Steps:

1. Employee Presence Check: Ensure that your calculation only includes employees who were present during the month. You can create a calculated column or measure that flags employees who were active (had any activity or were on leave).

2. Adjust SumWD Calculation: Modify your SumWD measure to only include employees who were present or had an activity, which can be done by checking both FactActivity and DimToT.

3. Measure for Employee Presence: Create a measure that identifies whether an employee had any presence during a given month:

EmployeePresence =
CALCULATE(
COUNTROWS(FactActivity),
FILTER(DimDate, DimDate[Date] >= DATE(2022,10,1))
) +
CALCULATE(
COUNTROWS(DimToT),
FILTER(DimDate, DimDate[Date] >= DATE(2022,10,1))
)

Dynamic UserDays Calculation: Adjust your UserDays to account for only those employees who were present, using the EmployeePresence measure:

AdjustedUserDays =
CALCULATE(
[SumWD] - [DaysOff],
EmployeePresence > 0
)

Average Activity Calculation: Finally, calculate your average activity by dividing the total activities by the adjusted UserDays:

AvgActivity =
DIVIDE([AllContacts], [AdjustedUserDays])

By doing this, you're dynamically excluding employees who had no presence in certain months, which will give you a more accurate average.