Not the usual Working Day Problem

Hi MFelix and thank you for the swift reply.

Let me try to clarify:

I tried to link the tables based on the respective Date fields ('Effort Date' in the work log and 'Date' in the DimDate table). However, once I do this, 'Effort Date' cannot be used as a hierarchical date field in visualizations anymore. See screen shots below.

Without Relationship between WorkLog and DimDates

No Relationship (Effort Date and Date)

Effort Dates as Hierarchy on X-AxisEffort Date Hierarchy

With Relationship between WorkLog and DimDates

Relationship between Effort Date an DateEffort Date not Hierarchical anymore

Individual Dates instead of Hierarchy

Regarding the measure: yes, this would only work, if there were a relation. An alternative would be to sum/count the 'weekday' in the actual work log table but there I face two more issues:

1. It is possible that there are no worklog entries on an actual work day, so the respective date would not even show up in the work log and would then be missing from the calculated number of work days.

2. I have multiple entries for any given day (appr. 200 employees). Hence, just counting the entries where 'weekday = 1' would give the wrong result.

Due to this, I think I need to count the working days in a separate ('neutral') table like DimDates, where I have exactly one entry for each date.

And this is the Catch22: If I do this and have NO relationship between WorkLog and DimDates, I cannot apply a date range filter across both tables.

I hope this makes it a bit clearer ...