Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-alq-msft
Community Support
Community Support

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:

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
v-alq-msft
Community Support
Community Support

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:

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.

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.