Count of Active Projects between two dates

[Anonymous]

My apologies if this exists already, i have been searching and found similar, but nothing that has worked when tested out, nor seems to fit this need.

I have a table with a Project ID, Start Date and End Date.   The End date will remain empty until the project is completed.   I need to show, based on the start and end dates, the number of projects that were active during any given time.

Any help anyone can give would be greatly appreciated.

Thank you!

TO

[Anonymous]

I got the counts like this:  (BTW sorry, my first table didn't post as neatly as the second so could be difficult to see): 

Program ID	Start Date	End Date	Count year(s)
123	1/22/2022	5/2/2022	2022
213	11/1/2021	6/1/2022	2021, 2022
432	10/1/2021	12/1/2021	2021
543	12/2/2020	4/3/2022	2020, 2021, 2022
546	4/2/2020	no end date	2020, 2021, 2022 (to current year)

So for 2020 there were 2 programs, for 2021 there were 4, and 2022 there are 4

I have been trying different things and I think I need to create a table that contains  records with program ID and a year (so if counted in multiple years there would be a record for each year). Then I can link this as a one to many from the Program table to report on all programs across all years. I saw examples of creating a table via DAX or the Power Query Advance Editor, but I don't know how to loop through creating the records for each year between the start and end (including the start and end). 

Hopefully that explains it better.

I hope the table comes across better this time...

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thank you. I want to understand what you did...I think (based on a lot of videos I watched yesterday) you created a Date table for the min and max date in the Data table, then linked that date table to program IDs with that date, right? But I don't see a start and end date for each program just one date. 

I was able to accomplish what I needed by (I think) doing something similar to what you did although I think it could be done better and more simplified. I copied the query that created my Program table and then added a column that was a list. This list was a list of years from start year to end year for that particular program. Then I expanded the list to be separate rows for each item in the list. This then gave me records for each program for each year, which I can then use to report on. I think though there is a better way to create this last table so it only contains the program ID and year (similar to one of yours). Mine is not a date table so it doesn't contain dates for everything inbetween like yours, it just contains that extra year field. I didn't know how in DAX or the Power Query how to create that table with the values and to link it to the original one so that all new rows would also get updated in my new one, so I simply copied the existing Query and renamed it because I didn't want all those extra rows in the initial one. So I think I ended up doing something like you did just slightly different. Thank you!

[Ashish_Mathur]

Hi,

My method creates one row for each month between the beginning and ending date.  You will see all steps in the Query Editor. 

[lpiz]

Hi Ashish, I think i could use your solution as well.

Could you reshare the file where one row for every motnh was created?

[Ashish_Mathur]

Hi,

Refer to this link - Generating Rows by Month for Date Ranges in Power Query | by Daniel Marsh-Patrick | Daniel Marsh-Pat...

[kirtanap]

helo,

i have a simple table with project id, yard, start and end date of multiple projects. i want to create a histogram that shows the no of active projects by week. eg: 

Project       Yard    Type        start             end

msc	china	NB	16/06/2023	20/03/2023
carnival	china	RF	17/06/2023	24/03/2023
maersk	korea	RF	20/06/2023	21/03/2023

eg: in this case, the histogram should have the value as 2 for the date17/6/23 as 2 projects are in progress and when i click on this, it should show me the project ID, yard of the 2 projects that are in progress. how can i achieve this best

[Ashish_Mathur]

Hi,

You may download my PBI file.

Hope this helps.

[Anonymous]

I am trying to do something similar I think...I can have projects that can span multiple years and I want a count of each project that occurred in each year...but stops at the current year...for example, the projects below would produce the counts at the  bottom. In each project is a LOB and so would like to be able to show the number of projects by LOB and year....I can add the LOB if if I can figure how to produce the counts by year...this should do it for as many years as the data contains (mostly only has about 3 years and not a large quantity)

Start	End
1/22/2022	5/2/2022
11/1/2021	6/1/2022
10/1/2021	12/1/2021
12/2/2020	4/3/2022
4/2/2020	no date

2020	2
2021	4
2022	4

So if I can make this a table and/or a chart (like a bar chart) it is what's needed... 

[Ashish_Mathur]

How did you arrive at the answers of 2,4 and 4?  Please give a proper explanation.

[v-lili6-msft]

Hi@ TO_CB

After my research, you can do these follow my steps like below:

Step 1:

Add a date table like below:

Step 2:

Cross join date table and project table

Table = FILTER(CROSSJOIN(Table1,'Calendar'),'Calendar'[Date]>=Table1[StartDate]&&'Calendar'[Date]<=(IF(Table1[EndDate] <>BLANK(),Table1[EndDate],MAX('Calendar'[Date]))))

Step 3:

Add the measure:

Measure = DISTINCTCOUNT('Table'[Project])

Result:

Here is demo , please try it

https://www.dropbox.com/s/gm154bq6hc7op0j/Count%20of%20Active%20Projects%20between%20two%20dates.pbi...

Best Regards,

Lin

[Ashish_Mathur]

Hi,

Share some data that i can work with.  I should be able to paste that data in an Excel file.  If possible, please also show the expected result.

[Anonymous]

Its a simple table, Project list with Start and finish dates.  Two Finish dates remain open because those projects are still active.  

Project	Start Date	Finish Date
Project 1	1/1/2018	5/7/2018
Project 2	2/8/2018
Project 3	5/5/2018	8/20/2018
Project 4	8/23/2018	8/24/2018
Project 5	6/5/2018
Project 6	7/2/2018	8/10/2018
Project 7	7/4/2018	8/9/2018

As i'm new to this, Its hard for me to show what the intended result should be visually...  using a date slicer (slider) i would like to be able to change the dates, and have a card visual show the count of projects that were Active between the dates of the Slider. So if the dates of the Slicer are 8/20/18 and 8/23/18, it should show a count of "4".

Hope this helps illustrate better

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

This is great, thank you both for the help.  however, the real data table I am using is Thousands of lines long.  A crossjoin table to have each possible date the project is open is not feasible due to size and performance concerns.  

[Anonymous]

I would think the dates between should work for this yes?

so something like

calculate(countrows(mydatatable),  datesbetween(mydatetable), date1, date2))

note you if you want the dates to be variable, I think  min(date1) and max(date2) should probably work

[Anonymous]

No, that doesn't seem to work.  I get a "DatesBetween and DatesinPeriod Function are only accepting date column reference as a first argument" error