Forum Discussion

arcall's avatar
arcall
Frequent Visitor
7 years ago

Total Headcount progression on current year

Hello All,

 

I am trying to achieve a total headcount visual for the following.

 

I have a table with the following columns.

Name of emp, hire date, termination date, Active(y or n), Class (Production or Support/...)

 

The hire date started many years ago.

 

I want to create the following visual that will show the overall headcount by month, but only show the current year up to the current month. Here is what i want to reproduce.

 

 

 

 

 

 

 

 

 

Your help is appreciated.

 

Thanks

Need Help

4 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, arcall 

    Here is a similar post for you refer to:

    https://community.powerbi.com/t5/Desktop/Measuring-logged-workdays/m-p/613517#M292418

    For your requirement, you just need to do some adjustment:

    Step1:

    For date table, add a tag for months up today of the current year in the complete date table.

    For example:

    tag = IF(YEAR('Date'[Date])=YEAR(TODAY())&&MONTH('Date'[Date])<=MONTH(TODAY()),"y","n")

    Step2:

    When create a crossjoin table, add this conditional "Date "[tag]="y" and Active="y"

    Step3:

    Then create a Total Headcount measure and then drag date and Class (from new table) in visual

    Total Headcount= CALCULATE(COUNTA('Table'[Name of emp]))

     

    Best Regards,

    Lin

    • arcall's avatar
      arcall
      Frequent Visitor
      7 years ago

      Hi Lin,

       

      I am not getting the visual I am expecting. 

       

      First, I just want to clarify about the date tag. I have a date table with a relationship to the databable (Called "HR Data"). I presume the date tag is under that date table and is a column ? Is the relationship between the date and HR Data will confuse this measure ?

       

      Here is the DAX command for tag.

       

      tag = IF(YEAR('Date'[Date])=YEAR(TODAY())&&MONTH('Date'[Date])<=MONTH(TODAY()),"y","n")
       

       

      In the page date filter, do I select all years ?

       

      Here is my crossjoin table :

       

      Table = FILTER(CROSSJOIN('Date','HR Data'),'Date'[tag]="y"&&'HR Data'[Active]="Yes"&&'Date'[Date]>='HR Data'[Hire Date]&&'Date'[Date]<='HR Data'[Termination/Release Date])
       
      I have a graphic visual with "Shared Axis" with Month from Date table amd Total HeadCount from the following measure.
       
      Total Headcount = CALCULATE(COUNTA('HR Data'[Name]))
       
      Here is what i've got. 
       
       
       
       
      I should have a cumulative amount and it should stop at April.
       
      What am I doing wrong ?
       
      Thanks

       

       

      • arcall's avatar
        arcall
        Frequent Visitor
        7 years ago

        I also used this in my measure 

        Total Headcount = CALCULATE(COUNTA('table'[Name])). doesn't work.