Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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...
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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...
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
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...
How did you arrive at the answers of 2,4 and 4? Please give a proper explanation.
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
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
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
96 | |
92 | |
84 | |
69 |
User | Count |
---|---|
160 | |
125 | |
116 | |
110 | |
95 |