Count Total of Employee

[borndy2904]

Hi all, 

I have an example table of employee

Name	join_date	resign_date
A	01/01/2020	01/01/2040
B	02/01/2020	02/01/2040
C	01/02/2020	01/02/2040
D	01/03/2020	01/03/2040
E	01/04/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 : 2
February : 3 --> (total of employee in january + employee join in february, and so on)
March : 4
April : 4

Is there anyone can help me?
Thanks in advance!

[tresesco]

With your simple example, the below approach would work. However, for your real data you might want to adjust few things taking clue from here.

Calc dim (or create a field in the script):  Month(join_date)   

Exp : RangeSum(Above(TOTAL
Count(If( join_date<=Today() and resign_date>=Today(), Name))
,0,RowNo(TOTAL)))

Also note : Month sort order is important this solution

[borndy2904]

Hi @tresesco , the expression you gave really works!

But is it possible to calculate the data from year to year because the data I have consists of several years (join date and resign date).
The above expression calculate the total only per year.

For example:
Name | join_date | resign_date
A | 01/01/2018 | 01/01/2040
B | 01/01/2019 | 01/01/2041
C | 01/01/2020 | 01/01/2042
D | 01/02/2020 | 01/02/2042

Expected result (in chart):
Month(dimension) : Total(measure)
January : 3
February : 4

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

Please help, thank you!