Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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:

 

amandaborges_0-1626725274874.png

 

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 😞

 

Thank you in advance!

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@Anonymous,

 

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]:

 

DataInsights_0-1626807944844.png

 





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

Proud to be a Super User!




View solution in original post

3 REPLIES 3
DataInsights
Super User
Super User

@Anonymous,

 

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]:

 

DataInsights_0-1626807944844.png

 





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

Proud to be a Super User!




Anonymous
Not applicable

@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.

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. 🙂





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

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors