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

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
Icon for Microsoft Employee rankMicrosoft 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