Number of open projects between open and close date at any date
I want to figure out how many project are open at any given time.
I define open as a project that has been started and not been closed yet.
What I have
A column with project id for all projects.
A column with start date for all the projects
A column with closed date for all the closed projects and the date 01/01/1970 for all the projects that has not been closed yet.
All this info is in the same table
What I try to get
I want to figure out how many projects was open at any given date and which projects. The end user need to be able to pick the dates. I imagen a diagram that goes up all days where the number of open projects are higher and goes down whenever a project is closed. I also imagen that you can figure out which projects was open at any given day. This will be used for further analysis of which project leaders are busy, hwich customers is helped etc.
I am not sure if it is best to make it as a measure or a new column.