Number of open projects between open and close date at any date

[ThomasWeppler]

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.

All feedback will be greatly appreciated.

[ThomasWeppler]

Thanks a lot AIB and Amitchandak.

I will take a closer look at your answers and see if I can solve my problem with your feedback.

[AlB · Accepted solution]

Hi @ThomasWeppler 

1. Create a date table. No relationships for the time being

2. Place DateT[Date] in the x axis of a (line) chart. If necessary create a slicer to select date ranges to show.

3. Create this measure and place it in Values of the chart:

Num open projects =
VAR currentDate_ = MAX ( DateT[Date] )
VAR closedProjectDate_ = DATE ( 1970, 1, 1 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[ ProjectID] ),
        Table1[StartDate] <= currentDate_,
        FILTER (
            ALL ( Table1[EndDate] ),
            Table1[EndDate] > currentDate_
                || Table1[EndDate] = closedProjectDate_
        )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@ThomasWeppler , see if my blog on similar topic help. You need to add code to handle the close date 01/01/1970 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...