cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
joonah Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-haibl-msft
Microsoft

Re: Headcount by month

@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
)

Headcount by month_1.jpg

 

Best Regards,
Herbert

View solution in original post

3 REPLIES 3
Omega Established Member
Established Member

Re: Headcount by month

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

 

joonah Frequent Visitor
Frequent Visitor

Re: Headcount by month

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

 

example.jpg

 

Thank you very much for your time,

Joona

Microsoft v-haibl-msft
Microsoft

Re: Headcount by month

@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
)

Headcount by month_1.jpg

 

Best Regards,
Herbert

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors