Forum Discussion

Frequent Visitor

9 years ago

Solved

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

Anonymous

Not applicable

9 years ago

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

Cheers

CheenuSing

Frequent Visitor

9 years ago

HiAnonymous

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

Eric

Anonymous
Not applicable
9 years ago
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
  New Member
  8 years ago
  But its not working at month level. showing same number at month level also
- neeraja
  New Member
  8 years ago
  But its not working at months level, showing same values as year
v-ljerr-msft
Microsoft Employee
9 years ago
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