Count records based on 2 date fields in same table

[emenard]

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?

[Anonymous · Accepted solution]

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

[v-ljerr-msft · Accepted solution]

Hi @emenard,

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

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

Regards

[emenard]

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.

[prateekraina · Accepted solution]

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

[Anonymous]

Hi @emenard

It is doable.  Can you please post some sample data in excel format on one drive and share the link.

Cheers

CheenuSing

[emenard]

Hi@Anonymous

There you go: https://1drv.ms/x/s!Apa9QmpiWxdWgQmWKtXN2pO47Pw0

Eric

[v-ljerr-msft · Accepted solution]

Hi @emenard,

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

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

Regards

[Anonymous · Accepted solution]

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

[neeraja]

But its not working at months level, showing same values as year

[neeraja]

But its not working at month level. showing same number at month level also