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 Super User!

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 Super User!

Helper III

thank you @PattemManohar!

Announcements