Forum Discussion
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
v-lili6-msftCommunity Support
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
Best Regards,
Lin
- Ashish_Mathur
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.
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
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.
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
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!
