I'm really struggling with this but I feel there must be a way of achieving it that I'm missing!
What I need is an accurate head count per month / week / day based on activity. Then we take the measures from the SQL (i.e. how many CV's sent out, jobs logged etc.) and divide that by the head count number.
Ideally what I need is the head count monthly to calculate against daily measures. I've tried using a date scaffold in weeks and months but because the data coming out of the CRM is daily (which it will need to be) I can't tie the 2 together. I've got a spreadsheet with the correct head counts per week and month but again I can't get the dates to tie together even if I expand that out into days.
The work book that is attached is not the one I'm using as haven't got far enough with it to add here but it is using the same data source. I've used a calculated field as:
IF [1stInterviews] > 0 THEN '1'
ELSEIF [2ndInterviews] > 0 THEN '1'
ELSEIF [3rdInterviews] > 0 THEN '1'
ELSEIF [BD Emails] > 0 THEN '1'
(There are more here but don't want to overcrowd the post)
I.E. if any one of those measures is greater than 0 then they must be active and so give it a 1 and then I count that for headcount. However what it's then doing is counting a 1 for every day of that month so I get an inaccurate number. I'm getting results of 44,857 active people for say Jan 2017 when I know that to be more like 450. I tried breaking that down to 1 measure I know is used daily but get the same name (albeit rather unsurprisingly).
There must be a way to drill down to this level but I'm just missing it entirely.
Can anyone help?