Count records based on 2 date fields in same table

Hi emenard

Try the following steps.

1. The date ( calendar) dimension table should not be linked to the employee table.

2. Create the meassure

 Started = CAlculate(Countrows(HRTable), FIlter (HRTable,HRTable[Employment start] >= Min('Calendar'[Date]) && HRTable[Employment start]<= MAx('Calendar'[Date])))

3. Ended = CAlculate(Countrows(HRTable), FIlter (HRTable,HRTable[Employment end] >= Min('Calendar'[Date]) && HRTable[Employment end]<= MAx('Calendar'[Date])))

4. Replace the HRTable with your employee table name and Calendar with your date dimension table.

5. Sample output based on the sample data.

If this works for you please accept this as solution and also give KUDOS.

Cheers

CheenuSing

Hi emenard,

Just finished the test, and the solution is the same as Anonymous's here. :smileylol:

In addition, here is the sample pbix file for your reference. :smileyhappy:

Regards

Hi emenard,

I have a different solution. Relate either start date or end date with date column of your dimension table and create these two measures. Thats it!

EmploymentStarted = CALCULATE(COUNT('Fact'[Employee]),FILTER('Fact','Fact'[End Date] = BLANK()))

EmploymentEnded = CALCULATE(COUNT('Fact'[Employee]),FILTER('Fact','Fact'[End Date] <> BLANK()))

Replace 'Fact' with your table name.



Prateek Raina