Forum Discussion
Rolling Count By Date
Hi jonahlawson ,
Create a measure something like this (I'm assuming you do not have a relationship between your calendar table and your employees table):
_noofHeadsInOrgOverTime =
VAR __cDate = MAX(calendar[date])
RETURN
CALCULATE(
DISTINCTCOUNT(yourTable[employeeID]),
FILTER(
yourTable,
yourTable[startDate] <= __cDate
&& (youTable[terminationDate] >= __cDate || ISBLANK(youTable[terminationDate]))
)
)
You then use calendar[date] (or calendar[month], calendar[year] etc.) on the axis of any visuals to see the balance at any point in time.
Pete
This works great except a need a way to make it a running/culmative total as time goes on. I have a culmative measure I created "Rolling Total = CALCULATE(DISTINCTCOUNT('Squad Aligment'[Person]), ALL('Squad Calender'), 'Squad Calender'[Date] <=MAX ('Squad Calender'[Date]))", but have yet to find a way to incorpate the subtraction of anyone terminated as well.
Hi jonahlawson ,
That's exactly what my measure does, although I think I made a mistake in my instructions. The version I gave actually WON'T work if your calendar table IS related to your employees table, so my apologies for that. I've updated my original post so as not confuse future readers.
If you do have your calendar table related to your employees table, then please try this measure instead:
_noofHeadsInOrgOverTime = VAR __cDate = MAX(calendar[date]) RETURN CALCULATE( CALCULATE( DISTINCTCOUNT(yourTable[employeeID]), FILTER( yourTable, yourTable[startDate] <= __cDate && (youTable[terminationDate] >= __cDate || ISBLANK(youTable[terminationDate])) ) ), CROSSFILTER(calendar[date], yourTable[date], none) )If you don't have this relationship in place, my original measure will work fine.
Either way, you need to give the measures a date context e.g. if you want to display the current value in a card, you'll need to put a visual-level filter on the card using a dimension from your calendar table filtered for 'today' or 'yesterday' or similar.
Pete
