cancel
Showing results for
Did you mean:
Regular Visitor

## How to calculate the number of active opportunities per month

Hi guys,

I'm new here and I have the following scenario:

I use the SalesForce opportunities base and I have a calendar table. The two are related by the opportunity's closing date. (Making it clear that in the Opportunity table, the data is not repeated)

My structure is as follows in the Opportunity table:

I need to make a table with the months of the year and how many active opportunities per month.

For example: Opportunity 1 opened in January and closed in July. It has to appear active in January, February, March, April, May, June and July.

How can I do this? I can't solve it 😞

1 ACCEPTED SOLUTION
Super User II

This solution uses a disconnected date table SlicerDate. It does not have any relationships with other tables.

Measure:

``````Active Opportunities =
VAR vMinDate =
MIN ( SlicerDate[Date] )
VAR vMaxDate =
MAX ( SlicerDate[Date] )
VAR vResult =
CALCULATE (
COUNT ( Opportunity[ID] ),
// creation date in current month
( Opportunity[Creation date] >= vMinDate
&& Opportunity[Creation date] <= vMaxDate )
// closing date in current month
|| ( Opportunity[Closing date] >= vMinDate
&& Opportunity[Closing date] <= vMaxDate )
// creation date in earlier month and closing date in later month
|| ( Opportunity[Creation date] < vMinDate
&& Opportunity[Closing date] > vMaxDate )
)
RETURN
vResult``````

Create visual with SlicerDate[Month]:

3 REPLIES 3
Super User II

This solution uses a disconnected date table SlicerDate. It does not have any relationships with other tables.

Measure:

``````Active Opportunities =
VAR vMinDate =
MIN ( SlicerDate[Date] )
VAR vMaxDate =
MAX ( SlicerDate[Date] )
VAR vResult =
CALCULATE (
COUNT ( Opportunity[ID] ),
// creation date in current month
( Opportunity[Creation date] >= vMinDate
&& Opportunity[Creation date] <= vMaxDate )
// closing date in current month
|| ( Opportunity[Closing date] >= vMinDate
&& Opportunity[Closing date] <= vMaxDate )
// creation date in earlier month and closing date in later month
|| ( Opportunity[Creation date] < vMinDate
&& Opportunity[Closing date] > vMaxDate )
)
RETURN
vResult``````

Create visual with SlicerDate[Month]:

Regular Visitor

@DataInsights , hey! 🙂

Thank you for your help! This works perfectly.

In my structure there is already a calendar table related to several other tables. I'll create one just for this kind of situation... I hope it won't be a problem.

I really appreciate it 🙂 Have a nice week.

Super User II

Hi Amanda,

Glad to hear that works. That's the correct approach--keep the main calendar table in your data model (with relationships to other tables).

Have a good week. 🙂

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors