Forum Discussion

Dimitris_Kats's avatar
Dimitris_Kats
Icon for Helper V rankHelper 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 th...
  • 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!



v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity 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
    Icon for Helper V rankHelper 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