Monhtly average activity challenge

[al1981]

Dear all

I have DimDate, DimToT, DimDaysMonth and FactActivity (of course many more, but those are for this challenge important ones)

DimDate is date table, DimToT is list of all absences of all employees, DimDaysMonth is table from Gov portal for all working days per month and FactActivity is table with all activities of employee with customers.
Challenge I have is to calculate average activity per day that would be exact from October 2022 on as our employees are changing and there are months where employees were not present yet at all. If I take average for all employees over last two years, I get for those wrong average. Any idea how I could solve this? For activity calculation I have now simple [AllContacts]/[UserDays], but [UserDays] its [SumWD](this is sum of monthly days from DimDaysMonth)-[DaysOff](sum of days of in DimToT)

Any idea what would be best approach? Somehow to check if Employee has no activity or any ToT and then to do the SumWD for this particular employee?

Thank you for any feedback...

[123abc · Accepted solution]

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.