Not the usual Working Day Problem

[skasper]

First, I searched and tested the variou solutions for calculating working days in this community and others. None of those applies to my situation as far as I could see.

Here is the situation:

• I have a table with work log entries. Obviously there are multiple entries for the same day (Effort Date).
• I have a DimDate table for the appropriate range.
• In both tables (due to the various solution attempts) I have a column to identify whether any given date was a work day or not (1 or 0).
• It is easy enough to count or sum these up and get a total of working days for any given time range.
• HOWEVER, as soon as I create a relationship between my work log table and the DimDate table (n:1), all the visualizations using the 'Effort Date' from the worklog table get messed up because 'Effort Date' cannot be used as a date hierarchy anymore. Also, any measures calculated using this field, don't work anymore.
• If I DO NOT set a relationship, I have no way of using 'Effor Date' as a date range filter to calculate and view the working days in any given range.

Take a look at the following screen shots:

Work Log Columns and Data

DimDate ColumnsDate Range Filter and Number of Week Days

And this is the Measure I am currently using (I realize that I would not need 'calculate' for the simple sum function, but as I said, I experimented a lot and simply left it there for conveniently adding filters etc.):

Week Days = 
CALCULATE(	
	SUM(DimDates[Weekday])
)

Any help would be much appreciated. Thank you.

Sascha

[MFelix]

HI @skasper,

Not abble to reproduce since the low information in your post, however if I understand correctly you want to calculate a number of measures based on the Effort Date, ion my previous experience with the this type of calculations and also having a DimDate table if I link the DimDate to the EffortDate your calculations mus be correct.

Sorry for asking but when you set-up the relationship are you making it in the correct date field in the work log entries?

One other thing that I notice is that you are making a slicer on the Effort_Date but then making the measure on your DimDate if you don't set-up your filter options in the relationship to both you are not abble to slice it back.

If you can share additional details I can help you better, but I believe it can be a small setup in your relationship.

Regards,

MFelix

[skasper]

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 ...

[MFelix · Accepted solution]

Hi @skasper,

As I said and based on your explanation the question is that you need to make you DimDate[date] as x-axis in your visuals to achieve the hierarchy and the correct values in your measures not the Effort Date since you have a relationship (that I assume is 1-Many) the visuals will be ok.

Regards,

MFelix

[skasper]

Frankly, I am not sure why I never thought of tackling it from that direction. I was just completely stuck on the Effort Date from the WorkLog.

Suffice to say, this works perfectly and you are my personal Hero of the Day.

Thank you for the fast and competent advice.

Sascha