Forum Discussion
Getting Head Count as on a particular date
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
2 Replies
v-qiuyu-msftCommunity Support
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 YuThanks 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.