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
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:
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)?