cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ØysteinTv Frequent Visitor
Frequent Visitor

Show data with dates from openended values?

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

1FI431012015-01-01 
2NO43100,82018-07-012018-12-31
3DK431012019-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? Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Show data with dates from openended values?

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.

 

PowerBI HR.png

 

PBIX Link

2 REPLIES 2

Re: Show data with dates from openended values?

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.

 

PowerBI HR.png

 

PBIX Link

ØysteinTv Frequent Visitor
Frequent Visitor

Re: Show data with dates from openended values?

Thanks a lot, almost got it now Smiley Happy 

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. Smiley Sad 


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

 

 

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 68 members 967 guests
Please welcome our newest community members: