Employee Headcount

[Shrujan1612]

Monthshort	Fy year	HEADCOUNT	CUMULATIVE HEADCOUNT
OCT	FY2023	6269	6269
NOV	FY2023	6314	12583
DEC	FY2023	6369	18952
JAN	FY2023	6450	25402
FEB	FY2023	6959	32361
MAR	FY2023	6997	39358
APR	FY2023	7049	46407
MAY	FY2023	7100	53507
JUN	FY2023	7240	60747
JUL	FY2023	7466	68213
AUG	FY2023	7544	75757
SEP	FY2023	7613	83370

my expected result is column cumulative headcount. I have already created measure for Headcount column it is working fine. Can anyone help me how to achieve the cumulative headcount values based on date selection cumulative should start from last 12 months.

Headcount =

VAR seledate = MAX(Employee[EMP_HIRE_DT])

RETURN

    CALCULATE(

            DISTINCTCOUNT(Employee[EMPLOYEE_NUM]),

           FILTER(ALL(Employee), seledate >= Employee[EFFECTIVE_FROM_DT] &&

            seledate <= Employee[EFFECTIVE_TO_DT]

    ),

    CROSSFILTER('Time'[CALENDAR_DATE],Employee[EMP_HIRE_DT],None))

[bhanu_gautam · Accepted solution]

@Shrujan1612 , Try using below measure

CumulativeHeadcount =
VAR MaxDate = MAX('Time'[CALENDAR_DATE])
VAR MinDate = EDATE(MaxDate, -11) -- This will get the date 11 months before the MaxDate
RETURN
CALCULATE(
SUM(Employee[Headcount]),
FILTER(
ALL('Time'),
'Time'[CALENDAR_DATE] >= MinDate && 'Time'[CALENDAR_DATE] <= MaxDate
)
)

[Anonymous · Accepted solution]

Hi, @Shrujan1612 

Thanks for the reply from bhanu_gautam, please allow me to provide addition:

Based on your information, I create sample tables:

Employee table

Table

Since I didn't have accurate information about the people, I just summarized it into how many people there were in each month when I created the sample Employee table.

Then create measures Headcount and cumulative headcount, try the following dax:

HEADCOUNT = SUM('Empolyee'[EMPLOYEE])

CUMULATIVE HEADCOUNT = 
VAR MaxDate = MAX('Time'[CALENDAR_DATE])
VAR MinDate = EDATE(MaxDate, -11) 
RETURN
    CALCULATE(
        [HEADCOUNT],
        FILTER(
            ALL('Time'),
            'Time'[CALENDAR_DATE] >= MinDate &&
            'Time'[CALENDAR_DATE] <= MaxDate
        )
    )

Create a table visual and put fields in table view, here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam · Accepted solution]

@Shrujan1612 , Try using below measure

CumulativeHeadcount =
VAR MaxDate = MAX('Time'[CALENDAR_DATE])
VAR MinDate = EDATE(MaxDate, -11) -- This will get the date 11 months before the MaxDate
RETURN
CALCULATE(
SUM(Employee[Headcount]),
FILTER(
ALL('Time'),
'Time'[CALENDAR_DATE] >= MinDate && 'Time'[CALENDAR_DATE] <= MaxDate
)
)

[Shrujan1612]

Headcount is a measure we cant use it in sum.

[bhanu_gautam]

@Shrujan1612 , try this then

CumulativeHeadcount =
VAR MaxDate = MAX('Time'[CALENDAR_DATE])
VAR MinDate = EDATE(MaxDate, -12)
RETURN
CALCULATE(
SUMX(
FILTER(
ALL('Time'),
'Time'[CALENDAR_DATE] >= MinDate &&
'Time'[CALENDAR_DATE] <= MaxDate
),
[Headcount]
)
)