Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
(I have set up a date table with periodnumbers and such, but can't really get it to work...
Any good tips? 🙂
Solved! Go to 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.
Proud to be a Super User!
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.
Proud to be a Super User!
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)?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |