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!



ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity 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
    Icon for Helper V rankHelper 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
      Icon for Community Champion rankCommunity 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

      • Dimitris_Kats's avatar
        Dimitris_Kats
        Icon for Helper V rankHelper V
        4 years ago

        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!