cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

@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 3
Established Member

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

Frequent Visitor

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

Microsoft

@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

Announcements

#### 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?

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

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