Forum Discussion

jonahlawson's avatar
jonahlawson
Frequent Visitor
3 years ago

Rolling Count By Date

I have a data set of a list of people within an organization with columns stating who they are, when they were hired and a column on their temrination date if they left the organization. I am working...
Need Help
BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
3 years ago

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

jonahlawson's avatar
jonahlawson
Frequent Visitor
3 years ago

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.