Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Using a month-slicer, I want the average number of employees by adding the number of employees active by the first of the month to the last of each month.
If I select January, February and March, I get this data:
Table: DimWorker
WorkerID | EmploymentStartDate |
1 | 01.01.2020 |
2 | 01.01.2020 |
3 | 15.01.2020 |
4 | 01.02.2020 |
Employees by start of January: 2
Employees by end of January: 3
Average: 2.5
Employees by start of February: 3
Employees by end of February: 4
Average: 3.5
Sum average: 2.5 + 3.5 / 2 = 3
I have been able to make measures that finds the number of employees by the start and end of month, but I don't know how to get the average for each individual month and add them up.
Measures:
Solved! Go to Solution.
Hi, @Anonymous
Based on my research, you may create measures as follows.
Active start of period =
var minD = CALCULATE ( MIN ( 'Table'[EmploymentStartDate] ), ALL ( 'Date' ) )
var thisD = DATE(YEAR ( MIN ( 'Date'[Date] ) ),MONTH ( MIN ( 'Date'[Date] ) ),1)
RETURN
IF (
YEAR ( MIN ( 'Date'[Date] ) ) = YEAR ( minD )
&& MONTH ( MIN ( 'Date'[Date] ) ) = MONTH ( minD ),
CALCULATE (
DISTINCTCOUNT ( 'Table'[WorkerID] ),
'Table'[EmploymentStartDate] = minD
),
IF(COUNTROWS('Table')<>BLANK(),
CALCULATE (
DISTINCTCOUNT ( 'Table'[WorkerID] ),
ALL('Table'),'Table'[EmploymentStartDate] <thisD
)
)
)
Active end of period =
var nextD = DATE(YEAR ( MIN ( 'Date'[Date] ) ),MONTH ( MIN ( 'Date'[Date] ) )+1,1)
RETURN
IF(COUNTROWS('Table')<>BLANK(),
CALCULATE (
DISTINCTCOUNT ( 'Table'[WorkerID] ),
ALL('Table'),'Table'[EmploymentStartDate] <nextD
)
)
avg =
AVERAGEX(GROUPBY('Date','Date'[Date].[Year],'Date'[Date].[MonthNo]),
(CALCULATE([Active end of period])+CALCULATE([Active start of period]))/2)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on my research, you may create measures as follows.
Active start of period =
var minD = CALCULATE ( MIN ( 'Table'[EmploymentStartDate] ), ALL ( 'Date' ) )
var thisD = DATE(YEAR ( MIN ( 'Date'[Date] ) ),MONTH ( MIN ( 'Date'[Date] ) ),1)
RETURN
IF (
YEAR ( MIN ( 'Date'[Date] ) ) = YEAR ( minD )
&& MONTH ( MIN ( 'Date'[Date] ) ) = MONTH ( minD ),
CALCULATE (
DISTINCTCOUNT ( 'Table'[WorkerID] ),
'Table'[EmploymentStartDate] = minD
),
IF(COUNTROWS('Table')<>BLANK(),
CALCULATE (
DISTINCTCOUNT ( 'Table'[WorkerID] ),
ALL('Table'),'Table'[EmploymentStartDate] <thisD
)
)
)
Active end of period =
var nextD = DATE(YEAR ( MIN ( 'Date'[Date] ) ),MONTH ( MIN ( 'Date'[Date] ) )+1,1)
RETURN
IF(COUNTROWS('Table')<>BLANK(),
CALCULATE (
DISTINCTCOUNT ( 'Table'[WorkerID] ),
ALL('Table'),'Table'[EmploymentStartDate] <nextD
)
)
avg =
AVERAGEX(GROUPBY('Date','Date'[Date].[Year],'Date'[Date].[MonthNo]),
(CALCULATE([Active end of period])+CALCULATE([Active start of period]))/2)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @v-alq-msft , that solved what I asked for. I have marked your solution as the answer.
For your AVG measure, I could not use
Date'[Date].[Year]
but could instead use
Date'[Year]
The same for month.
The Avg measure groups by the two date columns, but I also want it to be effected by my [Employee Country] column which is in the same table as DimWorker. I don't see any reason why it should not be effected by it, there are no filters. Any idea how to solve that one?
I also tried adding a filter in the measure(s), [Employee Country] = "Denmark", but it had no impact on the output.
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |