Headcount by month

[joonah]

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

[Omega]

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

[joonah]

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 · Accepted solution]

@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