Show data with dates from openended values?

[ØysteinTv]

Hi,

I'm trying to build a simple HR type dashboard and am looking at understanding best how to calculate the number of employees for the time periods I'd like to see using a time slicer.

For instance I want to see data for Q1 2019 and have a simple button slicer where I select year and quarter. 
My data has "Valid from" and "Valid to" fields, where "Valid to" is blank if the employee has no set departure date. Meaning they should count as long as the "valid from" is at or before the period selected to view data for. 
Table Employee

Employee ID Country_code Gender Employmentrate Valid From Valid to

1	FI	4310	1	2015-01-01
2	NO	4310	0,8	2018-07-01	2018-12-31
3	DK	4310	1	2019-01-01

So if I am looking at data for Q1 2019, it should give employee 1+3, for Q1+2 2018, employee 1 and employee 1+2 for Q3+Q4 2018. 

I looked at the HR sample dashboard, but didn't fully understand it's logic... It used two measures:

1: EmpCount = CALCULATE(COUNT([Empoyee ID]); FILTER(ALL('Date'[PeriodNumber]); 'Date'[PeriodNumber] = MAX('Date'[PeriodNumber])))
2: Actives = CALCULATE([EmpCount]; FILTER(Employee; ISBLANK(Employee[TermDate])))

(I have set up a date table with periodnumbers and such, but can't really get it to work... 
Any good tips? 🙂

[AnthonyTilley · Accepted solution]

i would create a seprate date table that is used for the selected values 

then create a max an min date value based on the slected dates of this table 

you can then use a simple if statment to check if the start and end date are between these two mesures and then filter a table based on this measure. 

i have included a link to a PBIX file that should have what your looking for.

PBIX Link

[ØysteinTv]

Thanks a lot, almost got it now 🙂 

How do I put this into a new measure to filter on it? Tried turning it into 0/1 instead, but still won't let me filter. 😞 

And if I use a slicer which selects Quarters for example, how can I get it to only select the persons active at the last date of the period (ie for Q1 I only want actives for 31.03 in one field, but in another I might want actives for 01.01 to use as a comparison method for growth)?