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
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. 

BA_Pete's avatar
BA_Pete
Super User
3 years ago

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