Forum Discussion
Count records based on 2 date fields in same table
Hi,
Very new to Power BI but I have been working with SQL Server SSAS (and SSIS) for a few years.
I'm trying to build a dataset from a sql view, which seemed simple enough but, it's starting to give me a headache.
The view return a list of all company employees, with an employment start and end date. The end date is null if the employee is still employed, obviously.
What i'm trying to acheive is a couple of cards and a date slicer. First card is showing to number of employees who started their employment, the other, the number of employees who's employment was terminated, all within a certain range of dates (I do have a datamart date table used in a olap cube).
I first tried to link both dates to the date table but soon realized one relationship was deactivated.
I then tried creating a second date table, from the datamart table, to link the second date from the employee table. No luck. It seem that all dates related to the second set, were showing a null date (1900-01-01).
I also tried creating a calculated column using Count and USERELATIONSHIP but somehow that failed too.
Any hints on how to create this? Was I on the right track at one point but was just missing something?
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,
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
8 Replies
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 RainaHi emenard
It is doable. Can you please post some sample data in excel format on one drive and share the link.
Cheers
CheenuSing
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
Thanks for all the help guys. Tried both solutions... works really well.
Thanks v-ljerr-msft for the sample file. Helped me out figuring some stuff out.
