Number at a particular date

Hi Mariusz ,

Looking at data again these are budgeted leavers (i.e. those we budget to leave this coming FY) this is what we have in the database with no start date but a particulary end date given our attrition modelling. What would you suggest to do as would actually like to keep these budgeted leavers in there but work out a way to tweak the formula to take account of null dates?

Hi tobiasmcbride 

What would be their start date as an assumption? 

At present the database assumes a non-existent one and not sure what we can do about that. Are there any tweaks we could make to ensure they still appear but to remove the null? Not sure what date we could use as a replacement here though instead of just having null? 

Apologies Mariusz forgot to tag you in reply above

Hi tobiasmcbride 

In the attached file, I've added myself as an extra record, as you can see I'm still aspiring in the slicer however you will not be able to see me in the headcount by date because it would not make a logical sense.

If you need to count this individual separately, you can create another measure like below.

Headcount no start date = 
CALCULATE(
    COUNTROWS( employee ),
    ISBLANK( employee[Start Date] )
)

Hi tobiasmcbride 

Another way would be selecting a date point in the future by replacing start date nulls with let's say today + 30 and allocating this individual there.

Thanks Mariusz managed to get it working eventually looking backwards from now.

I have budget headcount data too like so:

I want to display the chart with headcount data for the actuals and the budget line for 'latest plan'. However it seems to when displaying on the chart take the sum of all of these values for a particular time period so I get tens of thousands for the budgeted headcount and that remains fairly constant over time whilst the actual headcount data flucuates as you modelled. 

I think it has to do with the fact that the budget 'date' is only the end of the month or something but wondered if you could assist?

Hi Mariusz 

trying to get this to work looking forward in time and seems to only be working from today backwards. We have some confirmed starters in the data that we can look forward and, hence, compare with budget. still trying to work out how to deal with the data quality matter around null start dates to get most accurate position so if any further ideas on this would be great.