Forum Discussion

Dimitris_Kats's avatar
Dimitris_Kats
Helper V
4 years ago
Solved

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!!

 

  • ValtteriN's avatar
    ValtteriN
    4 years ago

    Hi,

    This is one way to do this:
    data:

    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:

    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!



6 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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)

    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.

    • Dimitris_Kats's avatar
      Dimitris_Kats
      Helper V
      4 years ago

      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's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    This is one way to do this:
    data:

    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:

    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!



    • Dimitris_Kats's avatar
      Dimitris_Kats
      Helper V
      4 years ago

      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 

      • ValtteriN's avatar
        ValtteriN
        Community Champion
        4 years ago

        Hi,

        Try changing the data type here:


        Or in the Powerquery:

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