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
Dimitris_Kats
Resolver I
Resolver I

Annual Headcount Average

Hello.

I was hoping you could help me calculate an annual average headcount.

I have a table with all the employees month by month, and I have a date table as well.

The employee table looks like that:

Date ID

Employee ID

20220101

  1234

20220101

  1235

20220101

  1236

20220101

  1237

20220101

  1238

20220101

  1239

20220201

  1234

20220201

  1235

20220201

  1236

20220201

  1238

20220201

  1239

20220301

  1234

20220301

  1235

20220301

  1236

20220301

  1238

20220301

  1239

20220301

  1240

20220301

  1241

 

For now I have the employees of January date ID 2022-01-01 (the first 6 rows) next month I will have all the employees again with date id 20220201 (the next 5 rows) etc.

 

I would like to calculate the average number of employees. For example, for January I will have the 6 employees / 1 = 6

When the February comes:  (6 (January) + 5 (February) )/2 (January +February) = 5,5

 

When the March comes: (6 (January) + 5 (February) + 7 (March) )/3  (January +February +March) = 6

 

I Managed to do it using the Date ID:  DIVIDE(COUNT(Table[Employee ID]), DISTINCTCOUNT(Table[Date ID]),0)

I would like to achieve the same result using the date table. Is it possible?

 

Thank you in advance!!

 

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

Hi,

This is one way to do this:
data:

ValtteriN_0-1643213125387.png

Dax:

RT empID average =
var maxdate = MAX('Calendar'[Date])
var rt =
CALCULATE (
COUNT('Average of empid'[ID]), --
ALL('Average of empid'),'Average of empid'[Date]<=maxdate)
var _monthnum = MONTH(MAX('Calendar'[Date]))
return

Divide(rt,_monthnum)


End result:
ValtteriN_1-1643213159849.png

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
v-xiaotang
Community Support
Community Support

Hi @Dimitris_Kats 

Try this, create the measure,

Measure = 
    var _start= VALUE(LEFT(MIN('Table'[Date ID]),4)&"0101")
    var _end= MIN('Table'[Date ID])
    var _countemployees =
    CALCULATE (
        COUNT ( 'Table'[Employee ID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date ID] >= _start
                && 'Table'[Date ID] <= _end
        )
    )
    var _countmonth =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date ID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date ID] >= _start
                && 'Table'[Date ID] <= _end
        )
    )
return DIVIDE(_countemployees,_countmonth)

vxiaotang_0-1643698414497.png

Best Regards,

Community Support Team _Tang

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

Hi @v-xiaotang ,

 

 
Thank you very much for replying to me. If I am not wrong, there is no use of the calendar table correct?
How can I use the calendar table to have the same results?

Thank you in advance. Your help is greatly appreciated

ValtteriN
Super User
Super User

Hi,

This is one way to do this:
data:

ValtteriN_0-1643213125387.png

Dax:

RT empID average =
var maxdate = MAX('Calendar'[Date])
var rt =
CALCULATE (
COUNT('Average of empid'[ID]), --
ALL('Average of empid'),'Average of empid'[Date]<=maxdate)
var _monthnum = MONTH(MAX('Calendar'[Date]))
return

Divide(rt,_monthnum)


End result:
ValtteriN_1-1643213159849.png

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @ValtteriN 

 

First i would like to thank you for your reply and your help.

I have an issue regarding the data type...the one calendar column is type text and the other type number :(...I am trying to resolve it.

Thank you very very much again for your help 

Hi,

Try changing the data type here:

ValtteriN_0-1644228328214.png


Or in the Powerquery:

ValtteriN_1-1644228369545.png

Or Finally by using functions e.g. VALUES or CONCANETATE





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you @ValtteriN ,

 

I managed to resolve my problem by changing your measure:

var maxdate = MAX('Time'[Calendar Date])

var rt =

CALCULATE (

COUNT(Table[Employee ID]), --

ALL('Time'),'Time'[Calendar Date]<=maxdate && Time [Calendar Date]>=min('Time'[Calendar Date]))

var _monthnum = MONTH(MAX('Time'[Calendar Date]))

return

 

Divide(rt,_monthnum)

Thank you very much again for your help!

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.

Top Solution Authors