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
