Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I got tables as below and using this dax to calculate the nr of employees in current month. However I can't get my head around how I should calculate the average on the last three months. Any good ideas?
Headcount =
VAR currentDate=IF(max(Calender[Date])>TODAY();TODAY();max(Calender[Date]))
Return
CALCULATE(
countrows(Employees);
FILTER(Employees;
Employees[FromDate]<= currentDate&&
Employees[ToDate]>= currentDate)
)
Tables:
Calender | |
Date | AccountingPeriod |
1..31 | 201801 |
1..28 | 201802 |
1..31 | 201803 |
1..30 | 201804 |
Employees | ||
ID | FromDate | ToDate |
1 | 2018-01-01 | 9999-12-31 |
2 | 2018-01-01 | 9999-12-31 |
3 | 2018-03-01 | 9999-12-31 |
4 | 2018-04-01 | 9999-12-31 |
Wanted result | ||
Accountingperiod | Headcount | Average 3 month |
201801 | 2 | 2,0 |
201802 | 2 | 2,0 |
201803 | 3 | 2,3 |
201804 | 4 | 3,0 |
Solved! Go to Solution.
try this code - it uses TOPN to get last 3 months, ADDCOLUMN to add [Headcount] and finally AVERAGEX to calculate the average on the output
Average 3 month =
VAR CurrentPeriod =
MAX ( Calender[AccountingPeriod] )
VAR Last3Periods =
ADDCOLUMNS (
TOPN (
3,
FILTER (
ALL ( Calender[AccountingPeriod] ),
Calender[AccountingPeriod] <= CurrentPeriod
),
[AccountingPeriod], DESC
),
"HeadcountLast3", [Headcount]
)
RETURN
AVERAGEX ( Last3Periods, [HeadcountLast3] )
try this code - it uses TOPN to get last 3 months, ADDCOLUMN to add [Headcount] and finally AVERAGEX to calculate the average on the output
Average 3 month =
VAR CurrentPeriod =
MAX ( Calender[AccountingPeriod] )
VAR Last3Periods =
ADDCOLUMNS (
TOPN (
3,
FILTER (
ALL ( Calender[AccountingPeriod] ),
Calender[AccountingPeriod] <= CurrentPeriod
),
[AccountingPeriod], DESC
),
"HeadcountLast3", [Headcount]
)
RETURN
AVERAGEX ( Last3Periods, [HeadcountLast3] )
Thanks! Beautiful piece of code that worked perfectly
What is the From Date and To Date the period in which employees stay in the company? are there cases when this is not in 9999 ?
do you want to perform the analysis only at month level ?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi
The fromDate and ToDate is the employment period. When ToDate is 9999 that means the employee is still active. If the employee had left the company it could say 2018-03-31 for example, then he shouldnt be added to the monthly value in april (but still two active months to the average)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |