Relating Date Table to Min/Max Dates

[garynorcrossmmc]

Hi all,

I am working on a Headcount Details report, where the users requested a filter to show all employees as of a certain date - so the filter will be called 'As Of Date.'  

I have indirect relationships built between a Date table (called Date) and a Fact table called HR FACT_WORKFORCEEVENT.  The relationships are for a 'From Date' (EFFFROMDATE) and a 'To Date' (EFFTODATE).  

Is there a way to create a calculated column (a flag) for all records where 

The EFFTODATE is >= Selected Date && The EFFFROMDATE is <= Selected Date?

[amitchandak]

@garynorcrossmmc , Refer my HR Blog on the similar topic

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

[garynorcrossmmc]

Hi @amitchandak, thank you for your response!

I actually used that measure before to count the amount of employees.  But what I'm trying to do is filter the fact table (HR FACT_WORKFORCEEVENT) to show the detail of that count.  So this detail tab would include all rows of those active employees.  How do I build that column to filter the table?