Active Employee slicer based on Start and End date

[Anonymous]

I have a table that contains the below columns:

Employee

1. [Employee ID]
2. [Start Date]
3. [End Date] (Blank if still active)

I would like to create a slicer that can show me all employees that were active between two dates, is that possible without enumerating a date column for each day the user was active? That would really blow up the size of my report..

[Ashish_Mathur]

Hi,

I doubt you will be able to get a complete solution without enumerating a date column using the Query Editor.  In the Query Editor, one can fairly simply create that date column.

[Anonymous]

Would you mind explaining how to go about that?

Would it be better/possible to enumerate the dates so that it only enumaretes by quarter or month? That way I'm not creating a row for every day over 5 - 10 years.

[Ashish_Mathur]

Hi,

You'd be better off enumerating the column date wise becuase that will allow you to use Time Intelligence functions.  You may read my Blog post where i used the enumeration technique in POwer QUery i.e. Query Editor - Split total patient hospitalisation days into multiple months.

Hope this helps.

[Anonymous · Accepted solution]

Hi @Ashish_Mathur,

Thank you for all your help. I ended up enumerating all the dates as you suggested and I really didn't experience a performance hit.

For reference, here's how I enumerated my column including the Power Query (M) used:

1. I created a duplicate table that only contained my three necessary columns
2. Created a custom column with the below Power Query (M)
   

   if [Start Date]<>null and [End Date]<>null and [End Date]>[Start Date]
   then
   List.Dates([Start Date],Duration.TotalDays([End Date]-[Start Date]),#duration(1,0,0,0))
   else if [Start Date]<>null then
   List.Dates([Start Date],Duration.TotalDays(Date.From(DateTime.LocalNow())-[Start Date]),#duration(1,0,0,0))
   else
   {null}

   I have some contingencies on my data that had to be checked. I had to make sure there was actually an [End Date] and that it was later than the [Start Date]. In my data, the [End Date] can be earlier than the [Start Date] because we tend to rehire employees, which updates the [Start Date]. If they are currently still active then I'm using todays current date as the end date.
3. Once the column is made, I expand the new column and remove the original [Start Date] and [End Date] column to limit the aditional information that's stored in the table.

[stack23]

Okay, I created the list as requested,  when I tried creating an Advanced Visual Level filter where item contains '3/5/2012' example, nothing is returning, nothing is returning for any dates, how do I fix this ?  Thanks,   I checked the values in the query editor, and I know they are correct at least, but filter is not working

[Ashish_Mathur]

You are welcome.

[stack23]

the link does not work anymore

[drewlewis15]

I would use a disconnected calendar table.  Basically, duplicate your calendar table and have a min and max date measure on that duplicate table.  Do not connect that table to anything.  Then, use the date from that disconnected table as your slicer, and reference the min and max date in your "active employee" measure.  As you adjust the date range on your slicer, the min and max date will change, but your main data table connected to your main calendar table will not be filtered by the slicer (which is what you want).  Now, those min and max dates are simply values for you to use in your logical test to deem if an employee was active in that date range.

[Anonymous]

This is perfect for measures, however I also have tables displaying data from the Employee table. Is there anyway that I can have that table filtered as well?