cancel
Showing results for
Did you mean:
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 IV

## 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
Regular Visitor

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

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?

Super User IV

## 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
Highlighted
Regular Visitor

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

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 IV

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

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!