Getting Head Count as on a particular date

[Anonymous]

I have a table as below. I am tryingt to get the headcount as on a particular date. For example, as on 12-Nov-2016, 5 people have joined and 2 have left, so the headcount stands at 3.

I have created two measures as below. But here I can only calculate as on today. Is there a way to pass a parameter instead of today? Or a different way of calculating?

Total Joiners = CALCULATE( COUNTA(HCRaw[Name]), DATESBETWEEN(HCRaw[JD].[Date], DATE(2016,1,1), Today()))

Total Leavers = CALCULATE( COUNTA(HCRaw[Name]), DATESBETWEEN(HCRaw[LD].[Date], DATE(2016,1,1), Today()))

+------+--------------+--------------+
| Name | JD                  | LD                 |
+------+--------------+--------------+
| ALSK  | 01-Aug-2016 | 12-Nov-2016 |
| IKL     | 09-Oct-2016  |                       |
| ASK   | 09-Oct-2016  |                       |
| ASL   | 10-Oct-2016  |                       |
| KOL   | 12-Nov-2016 |                       |
| IKL     | 15-Dec-2016 | 20-Jan-2017 |
| ABC   | 21-Dec-2016 | 24-Jan-2017 |
| DEF   | 20-Jan-2017  |                       |
| GHI   | 24-Jan-2017  | 10-Oct-2016 |
+------+--------------+--------------+

-Shashanka

[v-qiuyu-msft]

Hi @Anonymous,

Currently, query parameter can't be passed to the measure. In your scenario, to calculate joined / leave employees per day, you can create a calendar table like below:

Calendar = CALENDAR(MIN('HCRaw'[JD]),MAX('HCRaw'[LD]))

Then create three measures in the HCRaw table:

JoinedPerDay = CALCULATE(COUNTA(HCRaw[Name]),FILTER(ALL(HCRaw),'HCRaw'[JD]=MAX('Calendar'[Date])))

LeavePerDay = CALCULATE(COUNTA(HCRaw[Name]),FILTER(ALL(HCRaw),'HCRaw'[LD]=MAX('Calendar'[Date])))

Headcount = 'HCRaw'[JoinedPerDay]-'HCRaw'[LeavePerDay]

Please see attached .pbix file.

Best Regards,
Qiuyun Yu

[Anonymous]

Thanks @v-qiuyu-msft

I am looking for a different solution that what you've calculated. I do not want to know the HC for a particular day, I want to HC as on a particualr day. So it should consider overall HC.