cancel
Showing results for
Did you mean:
Helper III

## Sum of available people in a certain month

Hello,

I'm trying to create a measure that will show how many people are "available" in the current month.

As an example, here's a data from 2 (!) people: Jessia (who can fullfill 2 roles) and Sandy:

 Name Role Available from month Jessica Accountant 10 Jessica Financial Controller 10 Sandy Financial Controller 11

As result I need to see that we have 1 person available in October, but 2 people available in November (since Jessica has become available previously, and is still available).

Somehow adding SUM and DISTINCTCOUNT doesn't work... and then there are the months... suggestions?

1 ACCEPTED SOLUTION
Community Champion

@Olia Please try using below expression as "New Column"

```TotalAvailable =
VAR CurrMonth = CALCULATE(DISTINCTCOUNT(RoleAvailable[Name]),FILTER(ALL(RoleAvailable),RoleAvailable[Month]=EARLIER(RoleAvailable[Month])))
VAR PrevMonth = CALCULATE(DISTINCTCOUNT(RoleAvailable[Name]),FILTER(ALL(RoleAvailable),RoleAvailable[Month]=EARLIER(RoleAvailable[Month])-1))
RETURN CurrMonth + PrevMonth```

Proud to be a PBI Community Champion

2 REPLIES 2
Community Champion

@Olia Please try using below expression as "New Column"

```TotalAvailable =
VAR CurrMonth = CALCULATE(DISTINCTCOUNT(RoleAvailable[Name]),FILTER(ALL(RoleAvailable),RoleAvailable[Month]=EARLIER(RoleAvailable[Month])))
VAR PrevMonth = CALCULATE(DISTINCTCOUNT(RoleAvailable[Name]),FILTER(ALL(RoleAvailable),RoleAvailable[Month]=EARLIER(RoleAvailable[Month])-1))
RETURN CurrMonth + PrevMonth```

Proud to be a PBI Community Champion

Helper III

thank you @PattemManohar!

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors