cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasWeppler
Advocate I
Advocate I

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.

All feedback will be greatly appreciated.

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

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 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
ThomasWeppler
Advocate I
Advocate I

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
Super User III
Super User III

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 

SU18_powerbi_badge

View solution in original post

amitchandak
Super User IV
Super User IV

@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...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors