cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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
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:

c2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
Highlighted
Community Support
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:

c2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Highlighted

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. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors