Forum Discussion
Calculate Cumulative headcount numbers
Anonymous Why are you using TODAY? Where you have TODAY, this needs to be the termination date of your person.
Running Total Hires = VAR __Date = MAX(New_Hires[Effective Date]) RETURN COUNTROWS(FILTER(ALL(New_Hires),[Effective Date] <= __Date))
Anonymous I still don't see the formula for Total Hires. Here is what I would expect to see in a Running Total Hires measure:
Running Total Hires =
VAR __Date = MAX('Table'[Date])
RETURN
COUNTROWS(FILTER(ALL('Table'),[Date] <= __Date && [New Hire] = 1))Hi Greg_Deckler ,
Current measures i created are as follows
Anonymous Yes. What is currently going on is that each point in the line is calculated for a specific period like P3 FY22. Now, assuming that the dates are related to all of your other tables, when you calculate COUNT of Terminations or New_Hires, you are only catching the employees terminated or hired during that period. Thus, you need to override the context created by P3 FY22 and implement your own context. That is the purpose of the FILTER with the ALL. Effectively what this is saying is that "give me all hires prior to or equal to my current date. But, now that I know a little more about your data model, it would be something like:
Running Total Hires = VAR __Date = MAX('Table'[Date]) RETURN COUNTROWS(FILTER(ALL('New_Hires'),[Date] <= __Date))Thanks Greg_Deckler
i have attempted the formula suggested but doesnt seem to work for me as not splitting by the individual periodsRunning Total Hires = VAR __Date = MAX(New_Hires[Effective Date]) RETURN COUNTROWS(FILTER(ALL(New_Hires),TODAY() <= __Date))Do i need to insert another date in this formula where i have made the text bold?Anonymous Why are you using TODAY? Where you have TODAY, this needs to be the termination date of your person.
Running Total Hires = VAR __Date = MAX(New_Hires[Effective Date]) RETURN COUNTROWS(FILTER(ALL(New_Hires),[Effective Date] <= __Date))