cancel
Showing results for
Did you mean:
Highlighted
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

 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## 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.

2 REPLIES 2
Member

## 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.

Frequent Visitor

## Re: Show data with dates from openended values?

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

Announcements

#### How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 68 members 967 guests
Recent signins: