cancel
Showing results for
Did you mean:
Highlighted
Helper III

## Average number of employees per month based on first and last day of month

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:

Active start of period=
IF(SELECTEDVALUE(DimWorker[EmploymentStartDate]) <= FIRSTDATE('Date'[Date]),
COUNTROWS(DimWorker))

Active start of period Countrows =
SUMX(DIMWORKER, [Active end of period])

Active end of period=
IF(SELECTEDVALUE(DimWorker[EmploymentStartDate]) <= LASTDATE('Date'[Date]),
COUNTROWS(DimWorker))

Active end of period Countrows =
SUMX(DIMWORKER, [Active end of period])

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

Hi, @Preben

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.

2 REPLIES 2
Highlighted
Community Support

Hi, @Preben

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.

Highlighted
Helper III

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]``

``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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors