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

Active Employee slicer based on Start and End date

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

1 ACCEPTED SOLUTION

Accepted Solutions
jgalus Regular Visitor
Regular Visitor

Re: Active Employee slicer based on Start and End date

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.

 

View solution in original post

9 REPLIES 9
drewlewis15 Member
Member

Re: Active Employee slicer based on Start and End date

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.

jgalus Regular Visitor
Regular Visitor

Re: Active Employee slicer based on Start and End date

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?

Super User
Super User

Re: Active Employee slicer based on Start and End date

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.


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

Re: Active Employee slicer based on Start and End date

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.

Super User
Super User

Re: Active Employee slicer based on Start and End date

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.


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

Re: Active Employee slicer based on Start and End date

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.

 

View solution in original post

Super User
Super User

Re: Active Employee slicer based on Start and End date

You are welcome.


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

Re: Active Employee slicer based on Start and End date

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

stack23 Regular Visitor
Regular Visitor

Re: Active Employee slicer based on Start and End date

the link does not work anymore

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

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: 292 members 2,771 guests
Please welcome our newest community members: