I have a table with Staff data with columns DateEmployed & TerminationDate and a DateTable.
I would like to work out the number of people that started & left (which I used a count formula) as well as the net growth for all date periods.
The formula would count each DateEmployed as 1 & if an individual does not have a Termination Date then it would not count it. e.g. 4 people starts in June 2016 & 2 leaves in June 2016 giving me a net value of 2.
I would like to display all 3 dimensions of data in one graph as well.
My formula does not count correctly & brings up incorrect data.
Starting: Measure counting the people starting in each month or year.
Starting =
CALCULATE (
COUNT ( 'Table'[FullName] ),
FILTER (
ALL ( 'Table' ),
COUNTROWS (
FILTER (
'Table',
NOT ISBLANK ( [DateEmployed] )
&& MONTH ( EARLIER ( 'Table'[DateEmployed] ) ) = MONTH ( MAX ( DateTable[Date] ) )
)
)
)
)
DateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31)) Starting: Measure counting the people starting in each month or year. Starting =
CALCULATE (
COUNT ( 'Table'[FullName] ),
FILTER (
ALL ( 'Table' ),
COUNTROWS (
FILTER (
'Table',
NOT ISBLANK ( [DateEmployed] )
&& MONTH ( EARLIER ( 'Table'[DateEmployed] ) ) = MONTH ( MAX ( DateTable[Date] ) )
)
)
)
) Ending: Measure for counting the people ending in each month or year. Ending =
CALCULATE (
COUNT ( 'Table'[FullName] ),
FILTER (
ALL ( 'Table' ),
COUNTROWS (
FILTER (
'Table',
NOT ISBLANK ( [TerminationDate] )
&& MONTH ( EARLIER ( 'Table'[TerminationDate] ) )
= MONTH ( MAX ( DateTable[Date] ) )
)
)
)
) |
Net: Total for each month:
Total = ABS([Starting] - [Ending])