Forum Discussion
Employee Headcount
| 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))
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
)
)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.
4 Replies
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
)
)Headcount is a measure we cant use it in sum.
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]
)
)
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.
