Link 2 dates and count all projects related to them

[Magdelicia]

Hi i'm working on my Project management Dashboard and now i'm trying to represent a tricky KPI.

Quick explanation : Before a project is a "project" we have opportunities with vendors. Those opportunities has different stages : Start, In progress, Qualified, Validated or Out.  

- A project is a validated opportunity which actually started.

- Qualified mean there is a great chance that this opportunity become a project in the future. 

So i want to represent a bar graph which show 1) the percentage of qualified opportunities, 2) the percentage of validated opportunities and 3) the percentage of projects who started not long ago.

 

I have 1 DB called PROJECTS_DB with :

1) DateStartOpportunity (ex: opportunity created on 06/06/2017)

2) DateCloseOpportunity (ex: opportunity closed on 07/07/2017)

3)TypeOpportunity (Start, In progress, Qualified, Validated or Out)

4)DateStartProject (ex: Project 1 start on 07/07/2017)

5)ProjectName

So in Power BI, I created 3 measures  :

1) Qualified opportunies =

CALCULATE ((COUNT('PROJECTS_DB[typeOpportunity)), FILTER(PROJECTS_DB','PROJECTS_BD'[typeOpportunity]= "Qualified"))

2) Validated opportunies =

CALCULATE ((COUNT('PROJECTS_DB[typeOpportunity)), FILTER(PROJECTS_BD','PROJECTS_DB'[typeOpportunity]= "Validated"))

3) Started Projects = 

IF((DateStartProject= DateStartOpportunity, COUNT('PROJECTS_DB[ProjectName]),VALUE('PROJECTS_DB'[ProjectName])),0)

My third formula is my problem. I want to link a Project with an opportunity. I want him to understand that each time  DateClosedOppounity  = DateStartProject he should count how much projects there is.

But it doesn't work

[v-caliao-msft · Accepted solution]

@Magdelicia,

Create a calculated column.
CheckDate =  IF(DateStartProject= DateStartOpportunity,1,0)

Then create a measure 

Started Projects = 
CALCULATE ((COUNT('PROJECTS_DB[typeOpportunity)), FILTER(PROJECTS_BD','PROJECTS_DB'[CheckDate]= 1))

Regards,

Charlie Liao

[Magdelicia]

Thank you! 

[Magdelicia]

Someone please?