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

[Anonymous]

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

[v-alq-msft · Accepted 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.

[Anonymous]

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.