Filtering Page Visuals from Between Date Slicer Range

[jharsh]

I have a requirement to filter all the visuals on a page where the dates for the tables powering them are between the selected date slicer dates. I have created 2 measures on my date table for min and max date.  These are being calculated as

CALCULATE(MIN(DateTable[Date]),ALLSELECTED(DateTable[Date]))

and

CALCULATE(MAX(DateTable[Date]),ALLSELECTED(DateTable[Date]))

My Employee table is being used for a stacked bar chart.  I would like to only include Employees that have a termination date between the min and max selected slicer dates.  Because the slicer is being used for multiple visuals I do not have relationships configured from the DateTable to the Employee or other tables. The measures show the correct dates when viewed in a table visual but do not return the expected results when used in a computed column on my bar chart.  

[vanessafvg]

can you provide more context?  what tables are you using, what is plotted on what.  Sample  data is ideal to understand what is going on here and some pictures are useful too.

[jharsh]

Sure. For the employee filter. I am using 2 tables DateTable which only has 1 column of dates with a type of date time and the Employee table.  This has Hire Date (datetime), Employee Name, Termination Date (datetime nullable), Active Status (Boolean), and Job Category.  I can not always rely on IsActive as it doesn't change but when changing the date range the employee may "become" active if it falls before termination date.

DateTable Example

Employee Table Example for security purposes

Bar Chart

The plots are X Axis Job Category Values Count of Employee Names. From the above data examples I would like to only get a result of 4 if the end date of the slicers is 5/25/2021 or after and a count of 5 if it is 5/24/2021 or before.  I have tried using computed column IF(Employee[TerminationDate] >= MinDate && Employee[TerminationDate <= MaxDate,TRUE(),FALSE()) then filtering on Isvalid = true but I keep getting a count of 5 all the time.

[V-lianl-msft · Accepted solution]

Hi @jharsh ,

This blog can help you solve problems:

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.