Counting number of open deals on given date

[Anonymous]

Hey guys!

My data look like this:

I have a date table and a sales opportunities table

Opportunitie ID	Created at	Close date	Opp Name	Value
356	01/01/2019	30/06/2021	Microsoft	$1.000.000
357	05/06/2019	05/10/2021	Oracle	$500.000
358	10/11/2020	01/04/2021	Tesla	$234.000
359	10/05/2021	(blank)	Alibaba	$1.000

The table holds the date when the deal opportunitie is created, and when it closes (either won or lost deal). But I need a measure with the info of how many deals I have on any given date, (and other measure with how much pipeline value i have on that date) historicaly, in open deals. An open deal is a opportunitie that have already been created, but not closed yet on that given date, so it changes depending on the reference date I'm looking to. Please note close date may be blank.

Considering this, I want a column chart with a date axis, and on the other axis, the info of the open deals on each day.

Ex.: on 01/06/2021 I had 3 open deals, with a total pipeline value of $1.501.000. (I need this for all dates on my axis).

[ValtteriN]

Hi,

Here is one way to do this. 

Open deals = var _date = MAX('Calendar'[Date]) return

countx(filter(all(Deals),or(and(Deals[Close date]>_date,Deals[Created at]<=_date),

and(Deals[Close date]=BLANK(),Deals[Created at]<=_date))),[Opportunitie ID])

Open deals Value = var _date = MAX('Calendar'[Date]) return

sumx(filter(all(Deals),or(and(Deals[Close date]>_date,Deals[Created at]<=_date),

and(Deals[Close date]=BLANK(),Deals[Created at]<=_date))),[Value])

I hope this helps and if it deos consider accepting this as a solution!

[PaulOlding · Accepted solution]

Hi @Anonymous 

Here's a measure to get a count of open opportunities.  The Date table has no relationship to the Opportunities table for this.

Open Opportunities = 
VAR _Date = MAX('Date'[Date])
VAR _Result1 = 
COUNTROWS(
    FILTER(
        Opportunities,
        Opportunities[Created at] <= _Date && _Date <= Opportunities[Close date]
        )
    )
VAR _Result2 = 
COUNTROWS(
    FILTER(
        Opportunities,
        Opportunities[Created at] <= _Date && ISBLANK(Opportunities[Close date])
        )
    )
RETURN
    _Result1 + _Result2

The opportunity value measure can have a similar pattern, replacing the COUNTROWS with a SUMX

VAR _Result1 = 
SUMX(
    FILTER(
        Opportunities,
        Opportunities[Created at] <= _Date && _Date <= Opportunities[Close date]
        ),
    Opportunities[Value]
    )

[amitchandak]

@Anonymous , Check if this blog on the similar topic can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970