cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markholland Regular Visitor
Regular Visitor

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

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

 

 

5 REPLIES 5
Super User
Super User

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
markholland Regular Visitor
Regular Visitor

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

Hi @Ashish_Mathur,

 

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

 

01.PNG

 

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.

 

02.PNG

 

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? 

Super User
Super User

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
markholland Regular Visitor
Regular Visitor

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

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?

Super User
Super User

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

Hi,

 

That link is not valid.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 476 members 4,380 guests
Please welcome our newest community members: