Hi Community,
We are trying to get an average for a headcount for past completed years and YTD for 2023. Our headcount data set is an append from monthly headcounts as of the first of each month. We when we display the information using our DIM date table, we get the average number, but we are searching a measurement as our final objective is to measure turnover (Average headcount of a year/Total departures in a year).
Here is a sample of how the data set looks:
| Original_Hire_Date | Office | Length_of_Service_in_Months_from_Continuous_Service_Date | Employee_Type | Worker_Active | Cost_Center | Employee_ID | Hire_Date | Province | Effective Date | MonthlyHeadcountColumn | Year |
| 01-Jan-05 | Barrie | 174 | Director | 1 | AR | 5920 | 01-Jan-05 | Ontario | 01-Jul-19 | 1 | 2019 |
| 01-Jan-05 | Barrie | 157 | Director | 1 | AR | 5920 | 01-Jan-05 | Ontario | 01-Feb-18 | 1 | 2018 |
| 01-Jan-08 | Barrie | 138 | Director | 1 | AR | 71977 | 01-Jan-08 | Ontario | 01-Jul-19 | 1 | 2019 |
| 01-Jan-08 | Barrie | 121 | Director | 1 | AR | 71977 | 01-Jan-08 | Ontario | 01-Feb-18 | 1 | 2018 |
| 05-Sep-07 | Barrie | 141 | Director | 1 | AR | 5016 | 05-Sep-07 | Ontario | 01-Jul-19 | 1 | 2019 |
| 09-Dec-96 | Barrie | 270 | Employee | 1 | Fin | 1845 | 09-Dec-96 | Ontario | 01-Jul-19 | 1 | 2019 |
| 09-Dec-96 | Barrie | 253 | Employee | 1 | Fin | 1845 | 09-Dec-96 | Ontario | 01-Feb-18 | 1 | 2018 |
| 04-Sep-07 | Toronto | 141 | Employee | 1 | HR | 5057 | 04-Sep-07 | Ontario | 01-Jul-19 | 1 | 2019 |
| 04-Sep-07 | Toronto | 124 | Employee | 1 | HR | 5057 | 04-Sep-07 | Ontario | 01-Feb-18 | 1 | 2018 |
| 07-Aug-07 | Toronto | 142 | Employee | 1 | HR | 5186 | 07-Aug-07 | Ontario | 01-Jul-19 | 1 | 2019 |
| 07-Aug-07 | Toronto | 125 | Employee | 1 | HR | 5186 | 07-Aug-07 | Ontario | 01-Feb-18 | 1 | 2018 |
"""Here is the measure that we were trying to use:
AverageHeadcountYTD2023 =
CALCULATE(
AVERAGE('LD Headcount'[MonthlyHeadcountColumn]),
DATESYTD('LD Headcount'[Effective Date], DATE(2023, 12, 31))
)
This dax gives the follwign error: "Only constant date value is allowed as a year end date argument."
AverageHeadcountYTD2023 =
VAR MaxDate = MAX('LD Headcount'[Effective Date])
RETURN
CALCULATE(
AVERAGE('LD Headcount'[MonthlyHeadcountColumn]),
'LD Headcount'[Effective Date] <= MaxDate,
YEAR('LD Headcount'[Effective Date]) = 2023
)
This gives the following error: This was just calculating for 2023 and was returning a value of 1 for 2023
Then we tried the following formula:
AverageHeadcountYTD2023 =
VAR MaxYear = YEAR(TODAY())
RETURN
SUMMARIZE(
FILTER(
'LD Headcount',
YEAR('LD Headcount'[Effective Date]) <= MaxYear
),
'LD Headcount'[Year],
"AverageHeadcount",
AVERAGE('LD Headcount'[MonthlyHeadcountColumn])
)"""
This gave us the error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
What are we doing wrong, or do you have any tips or tricks that coudl help us achieve the Year and Year to Date average in a measure?
@amitchandak
