Filter Calculated Column Dynamically - Show Results for Former Employees based on Date Range

[markholland]

Hi,

I wonder if someone can help me with a particular conundrum.

I have a report looking at employee results this year. Since the start of the year some employees have left but there results will remain in the report as there's revenue attached to it so that I can look back at any point in time as see the revenue. If I filter my report to last week I want to be able to see everyone employeed after the start date of that week. Similarly if I go back 2 months and 2 former employees were working, I want to see their names and results.

I have a table with a list of employees with start and end dates. I've created a column on this dataset that gives Today as the date if the employee is an active employee. If the employee is not active it shows the last date of employment.

I have another table that shows results for colleauges. I've used the date from this table to filter my visuals showing results for colleagues. At the moment it shows the Week Commencing date, so we can look weekly. However the option is there to select a range of Week Commencing dates from this table.

I've like to be able to use the date filter on my resulst table to filter the list of employees to show employees that would have been active by the MIN of whatever value is selected on this slicer.

How do I go about doing that?

As always, help is very much appreciated.

Thanks,

Mark

[Ashish_Mathur]

Hi,

You must have a Calendar Table with a relationship from the Date column of your data Tables to the date column of the Calendar Table.  In the Calendar Table, you must have a Week number column (use the WEEKNUM()) function.  In the slicer drag the Weeknum column from the Calendar Table.  Now with this formula, you will get the first date of the selected week

=MIN(Calendar[Date])

Hope this helps.

[markholland]

Hi @Ashish_Mathur,

I'm afraid this isn't what I'm looking for. I'll add some images to help.

Here are my data tables and relationships. I have a Calendar Table, User Table (unique list of employees) and a TimeEntries table, which shows the hours everyone has worked, where and when. As you can see both the Calendar and User tables link into the TimeEntries table.

This is a view of the User table. We don't have a final date of employment in our system, so I've worked around this by creating a column that says if they're not an active user, the give the last updated date value - which would have been when they were marked as not active. Otherwise, give today's date.

I'll have a slicer on the report that filters by Week Commencing date, from either the Calendar or TimeEntries table.

How can I get the list of Users to filter depending on the Week Commencing date so show only the employees with an Active Date greater than or equal to the slicer value? There will be instances where the slicer has multiple values so I would need to show those employees with Active Dates greater or equal to the MIN value in the slicer.

Does that help? 

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  Also, it will be great if you can show the expected result in a simple Table for a few items chosen in the slicer.

[markholland]

Hi @Ashish_Mathur,

I can't attach a link to the file I'm afraid, there's sensetive information on there. So I've mocked up a really simple example of what I'm trying to achieve. Click here to access it.

Imagine in Week 1 I have 8 active employees. In that week only 7 of them record hours worked. I would want my chart to show all 8 employees even with the employee who didn't record time. In Week 2, 2 of the employees are now longer active, so when this week is selected on the report they shouldn't appear at all.

I want a date slicer to allow me to swtich between these.

Does that help?

[Ashish_Mathur]

Hi,

That link is not valid.