cancel
Showing results for
Search instead 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

Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper III

thank you @PattemManohar!

## Helpful resources

Announcements

#### Check it Out!

Click here to read more about the December 2021 Updates!

#### Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

#### Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors