Forum Discussion

joonah's avatar
joonah
New Member
8 years ago
Solved

Headcount by month

Hello,

 

I am trying to solve how to make a measure that counts how many people are employed on a given year and month.

 

The parameter is that if a person is working on the last day of the month he/she should be inculded in the headcount - otherwise not.

 

I have a table that has true/false (1 or 0) colum for each date rows where 1 marks that the the person is employed on the day.

 

I am a beginner with DAX, so I would really appreciate any insight how to get going. Thank you very much!

 

br,

Joona

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    8 years ago

    joonah

     

    You can use PREVIOUSMONTH and PREVIOUSYEAR function in such scenario. I'd like to give a simple sample as below. You can adjust the formula according to your dataset.

     

    Please refer to the attachment for the sample table. I also create a Calendar table in PBI, and make relationship for them.

    Then you can use following two measures to get the desired result. For details, please refer to attached PBIX file.

     

    Headcount by month = 
CALCULATE (
    DISTINCTCOUNT ( Table1[PersonKey] ),
    PREVIOUSMONTH ( 'Calendar'[Date] ),
    Table1[HCCount] = 1
)

    Headcount by year = 
CALCULATE (
    DISTINCTCOUNT ( Table1[PersonKey] ),
    PREVIOUSYEAR ( 'Calendar'[Date] ),
    Table1[HCCount] = 1
)

     

    Best Regards,
    Herbert

3 Replies

  • Omega's avatar
    Omega
    Impactful Individual
    8 years ago

    Can you please share a screenshot of the table you are using? 

     

    • joonah's avatar
      joonah
      New Member
      8 years ago

      Hi,

       

      HCCount column displays whether the person is employed on a given day. Date key is the only datefield in this table, but I have calendaer table also. I was thinkin of adding date column to this table also...

       

       

      Thank you very much for your time,

      Joona

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        8 years ago

        joonah

         

        You can use PREVIOUSMONTH and PREVIOUSYEAR function in such scenario. I'd like to give a simple sample as below. You can adjust the formula according to your dataset.

         

        Please refer to the attachment for the sample table. I also create a Calendar table in PBI, and make relationship for them.

        Then you can use following two measures to get the desired result. For details, please refer to attached PBIX file.

         

        Headcount by month = 
CALCULATE (
    DISTINCTCOUNT ( Table1[PersonKey] ),
    PREVIOUSMONTH ( 'Calendar'[Date] ),
    Table1[HCCount] = 1
)

        Headcount by year = 
CALCULATE (
    DISTINCTCOUNT ( Table1[PersonKey] ),
    PREVIOUSYEAR ( 'Calendar'[Date] ),
    Table1[HCCount] = 1
)

         

        Best Regards,
        Herbert