Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Count of Active Projects between two dates

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

17 REPLIES 17
Anonymous
Not applicable

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 IDStart DateEnd DateCount year(s)
1231/22/20225/2/20222022
21311/1/20216/1/20222021, 2022
43210/1/202112/1/20212021
54312/2/20204/3/20222020, 2021, 2022
5464/2/2020no end date2020, 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...

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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!

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

mscchinaNB16/06/202320/03/2023
carnivalchinaRF17/06/202324/03/2023
maerskkoreaRF20/06/202321/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

Hi,

You may download my PBI file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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)

StartEnd
1/22/20225/2/2022
11/1/20216/1/2022
10/1/202112/1/2021
12/2/20204/3/2022
4/2/2020no date

 

20202
20214
20224

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-lili6-msft
Community Support
Community Support

Hi@ TO_CB

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

Step 1:

Add a date table like below:

1.jpg

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:

2.png

Here is demo , please try it

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

 

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

 

ProjectStart DateFinish Date
Project 11/1/20185/7/2018
Project 22/8/2018 
Project 35/5/20188/20/2018
Project 48/23/20188/24/2018
Project 56/5/2018 
Project 67/2/20188/10/2018
Project 77/4/20188/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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.