Snapshot table in a measure

[frankvb]

I have to get a snapshot (the ammount of values in the pipeline), but i dont have a date to use.
This is a simplified version of the data:

Project	date_request	date_decision	Decision	date_closed
project A	01/01/2002	01/05/2002	Approved	02/05/2002
project B	04/01/2003	07/06/2002	Approved
project C	31/01/2002	01/03/2004	Denied	01/03/2004
project D	15/05/2006		Undecided
project E	09/02/2007		Undecided

It is easy enough to show what is in the pipeline right now.

I just have to filter on decision and closing date.

But also need a snapshot at the end of each month showing how many projects where outstanding at that point.

So I need to be able to say at the end of januari 2002: there were 2 outstanding projects (A and C).
This seems quite complex.

What I am also have a hard time with, is to use this table as a timeline in a visual. Which one to choose.

Anybody else encountered a similar problem?

I tried with this, but it does not make much sense: 

Pipeline  =

VAR X = ENDOFMONTH(date_request)

VAR ThenInPipeline = calculate(

distinctcount([Project]),

[date_request] < X,

[date_decision] > X)

RETURN

ThenInPipeline

[frankvb]

@amitchandak thanks once again!

[amitchandak · Accepted solution]

@frankvb , refer if this blog on HR can help you - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

It deal with 2 dates and Active(Current employee), a Very similar approach. - https://www.youtube.com/watch?v=e6Y-l_JtCq4