Count total data per month in each year

[borndy2904]

Hi all, 

I have an example table of employee

Name	join_date	resign_date
A	01/01/2018	01/01/2040
B	01/07/2019	01/07/2041
C	01/01/2020	01/01/2042
D	01/02/2020	01/02/2040
E	01/03/2020	01/01/2021

*green: still exist
  red: resign

I want to create a measure to be added in the chart that can count total of employee that still exist (exist mean : join_date<=Today() and resign_date>=Today(). And I want it to be able to calculate total of employee per month in each year.
(*the dimension of chart is Month)

My expected result (in chart):

Month(dimension) : Total(measure)
January : 3 -> (count from 2018, and so on)
February : 4 --> (total of employee(exist) in january + employee join in february - employee resign, and so on)
March : 4

*I filtered the chart, only show data in 2020 (but the calculation remains from the previous year)

I've tried this one too
Exp : RangeSum(Above(TOTAL
Count(If( join_date<=Today() and resign_date>=Today(), Name))
,0,RowNo(TOTAL)))
but the expression above is calculate total per year only,
example result from expression above (using table above):
*filtered in 2020*
January: 1
February: 1
March: 0

Is there anyone can help me?
Thanks in advance!

[GaryGiles]

Try this:

If you create an island table in your load script with the month begin date [IslandMonth] that is not joined to your data model; then add IslandMonth as your dimension and for your measure, use:

count(if(join_date<=MonthEnd(IslandMonth) and resign_date>=IslandMonth,Name))

If you can't add a table to your data model, you could create a variable, vIslandMonth and define it as a ValueList:

ValueList('01/01/2018','02/01/2018','03/01/2018','04/01/2018', . . . )

Then, for your table dimension, use $(vIslandMonth) and your measure expression, use:

count(if(StartDate<=MonthEnd($(vIslandMonth)) and EndDate>=$(vIslandMonth),Employee))

You may have to make some adjustments, depending on your date field format.