Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
chitti5
Helper I
Helper I

Line graph to show count of projects

‎02-03-2025 06:13 PM

Hi,

 

I need assistance in displaying the number of in-process projects per month. Currently, I have 330 projects in process as of February 2025. I want to create a line chart that shows the number of in-process projects I had each month. For example, I had 356 projects as of November 2024 from the time the projects were created, 350 projects as of December 2024 where I had 10 projects completed and 4 projects newly created i.e. 356-10+4 = 350, and 338 projects as of January 2025. Please suggest a measure to share the line graph as I want to show the number of projects that were "in process" each month. 

 

Thanks in advance.

 

Labels:
Message 1 of 12
519 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable
In response to chitti5

‎02-04-2025 10:45 PM

Hi @chitti5 ,

 

According to your statement, I update the table and I think it should look like as below.

vrzhoumsft_0-1738737692697.png

I suggest you to create a Calendar table to help calculation.

 

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",FORMAT([Date],"MMM"),"MonthSort",MONTH([Date]))

 

Measure:

 

Count Project in Process =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Project Name] ),
    FILTER (
        'Table',
        'Table'[Project Created date] <= MAX ( 'Calendar'[Date] )
            && OR (
                'Table'[Project Completed date] > MAX ( 'Calendar'[Date] ),
                'Table'[Project Completed date] = BLANK ()
            )
    )
)

 

Result is as below.

vrzhoumsft_1-1738737761343.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Line graph to show count of projects.pbix
Message 10 of 12
343 Views
0
11 REPLIES 11
parry2k
Super User
Super User

‎02-04-2025 03:57 PM

@chitti5 you still didn't answer why Jan 2024 (project 11) is not getting included? Am I missing something?

 

11 Monday, January 15, 2024  


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 12 of 12
368 Views
0
parry2k
Super User
Super User

‎02-04-2025 03:46 PM

@chitti5 also earlier you mentioned that completed projected should be included in-process in the completed month. check your previous posts.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 11 of 12
386 Views
0
parry2k
Super User
Super User

‎02-04-2025 03:44 PM

@chitti5 row 11 - there is one in Jan 2024 as well.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 8 of 12
387 Views
0
chitti5
Helper I
Helper I
In response to parry2k

‎02-04-2025 03:54 PM

@parry2k Apologies for the confusion, This is totally my mistake. That is January 2025 for Row 11. Also, I mentioned the count of completed projects should be subtracted from that month ( For example, one project started in September 2024 and was completed on February 1, 2025. This project should be shown as "in process" for the months of September, October, November, December 2024, and January 2025, but it should not be counted as in process for February 2025 as it got completed. - This is what I mentioned) Sorry for the confusion and thank you so much for trying to help me out. 

 

Can you please share the correct measure by the above statement and below mentioned possible answers.

 

The ideal result should be:

Sep - 1

Oct - 7

Nov - 11

Dec - (11 from nov, +5 newly created Dec, - 2 projects completed in dec) = 14

Jan'25 - (14 from dec, +1 newly created in Jan, -3 projects completed in Jan) = 12

 

Message 9 of 12
371 Views
0
Anonymous
Not applicable
In response to chitti5

‎02-04-2025 10:45 PM

Hi @chitti5 ,

 

According to your statement, I update the table and I think it should look like as below.

vrzhoumsft_0-1738737692697.png

I suggest you to create a Calendar table to help calculation.

 

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",FORMAT([Date],"MMM"),"MonthSort",MONTH([Date]))

 

Measure:

 

Count Project in Process =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Project Name] ),
    FILTER (
        'Table',
        'Table'[Project Created date] <= MAX ( 'Calendar'[Date] )
            && OR (
                'Table'[Project Completed date] > MAX ( 'Calendar'[Date] ),
                'Table'[Project Completed date] = BLANK ()
            )
    )
)

 

Result is as below.

vrzhoumsft_1-1738737761343.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Line graph to show count of projects.pbix
Message 10 of 12
344 Views
0
parry2k
Super User
Super User

‎02-04-2025 02:49 PM

@chitti5 hello see attached, I assume this is what you are looking for.  Tweak the solution as you see fit. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Project In Process.pbix
Message 6 of 12
419 Views
0
chitti5
Helper I
Helper I
In response to parry2k

‎02-04-2025 03:41 PM

Hi @parry2k Based on the report you provided, it appears that there is one project counted for each month from January 2024 to August 2024, even though my data starts from September 2024. Ideally, there should be only one project listed for September 2024, but I see two projects instead.

 

The ideal result should be:

Sep - 1

Oct - 7

Nov - 11

Dec - (11 from nov, +5 newly created Dec, - 2 projects completed in dec) = 14

Jan'25 - (14 from dec, +1 newly created in Jan, -3 projects completed in Jan) = 12

 

Please verify the data and suggest me the correct measure to apply for my data as I have more than 250 projects in my company.

Message 7 of 12
388 Views
0
chitti5
Helper I
Helper I

‎02-04-2025 02:27 PM

Hi @rohit1991 . Thank you for responding. I tried those steps. But it didn't work out.

 

I need help visualizing project progress over time in Power BI. I have a table that includes 18 projects, each with Project Created dates and Project Completed dates. For example, one project started in September 2024 and was completed on February 1, 2025. This project should be shown as "in process" for the months of September, October, November, December 2024, and January 2025, but it should not be counted as in process for February 2025 as it got completed.

 

I want to create a measure or calculated column that shows the total count of "in process" projects for each month . This will be displayed in a line chart, with calendar months on the X-axis and the count of "in process" projects on the Y-axis.

 

Could you please provide a reference Power BI file that demonstrates how to achieve this using my data?

 

Thank you!

 

Project NameProject Created dateProject Completed date
1Monday, September 16, 2024Saturday, February 1, 2025
2Sunday, December 1, 2024 
4Wednesday, November 20, 2024 
5Saturday, October 12, 2024Friday, January 31, 2025
6Tuesday, October 15, 2024Monday, February 3, 2025
7Friday, December 20, 2024 
8Friday, December 20, 2024 
9Friday, December 20, 2024Wednesday, January 15, 2025
10Friday, December 20, 2024 
11Monday, January 15, 2024 
12Friday, November 15, 2024 
13Friday, November 15, 2024 
14Tuesday, October 15, 2024Sunday, December 15, 2024
15Tuesday, October 15, 2024Friday, January 31, 2025
16Tuesday, October 15, 2024 
17Tuesday, October 1, 2024Tuesday, December 17, 2024
18Saturday, November 2, 2024 
Message 5 of 12
431 Views
0
rohit1991
Super User
Super User

‎02-03-2025 10:37 PM

Hi @chitti5 ,

To track the number of in-process projects per month, you need a measure that:

  1. Counts projects created before or during each month
  2. Subtracts completed projects

DAX Measure: Count of In-Process Projects

InProcessProjects =
VAR SelectedMonth = MAX('Calendar'[Date])
VAR CreatedProjects =
    CALCULATE(
        COUNTROWS('Projects'),
        'Projects'[CreatedDate] <= SelectedMonth
    )
VAR CompletedProjects =
    CALCULATE(
        COUNTROWS('Projects'),
        'Projects'[CompletedDate] <= SelectedMonth
    )
RETURN
CreatedProjects - CompletedProjects

Steps to Implement:

  1. Ensure you have a date table (Calendar) with continuous dates.
  2. Add this measure to a line chart with Calendar[Month] on the X-axis.

This will dynamically show active in-process projects per month based on project creation and completion.


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Message 4 of 12
455 Views
0
parry2k
Super User
Super User

‎02-03-2025 06:55 PM

@chitti5 can you share the share of raw data to provide a solution?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Message 2 of 12
478 Views
0
chitti5
Helper I
Helper I
In response to parry2k

‎02-04-2025 02:29 PM

Hi @parry2k Thank you for responding. Here is the raw data you've asked for. Please share the solution.

 

I need help visualizing project progress over time in Power BI. I have a table that includes 18 projects, each with Project Created dates and Project Completed dates. For example, one project started in September 2024 and was completed on February 1, 2025. This project should be shown as "in process" for the months of September, October, November, December 2024, and January 2025, but it should not be counted as in process for February 2025 as it got completed.

 

I want to create a measure or calculated column that shows the total count of "in process" projects for each month . This will be displayed in a line chart, with calendar months on the X-axis and the count of "in process" projects on the Y-axis.

 

Could you please provide a reference Power BI file that demonstrates how to achieve this using my data?

 

Thank you!

 

Project NameProject Created dateProject Completed date
1Monday, September 16, 2024Saturday, February 1, 2025
2Sunday, December 1, 2024 
4Wednesday, November 20, 2024 
5Saturday, October 12, 2024Friday, January 31, 2025
6Tuesday, October 15, 2024Monday, February 3, 2025
7Friday, December 20, 2024 
8Friday, December 20, 2024 
9Friday, December 20, 2024Wednesday, January 15, 2025
10Friday, December 20, 2024 
11Monday, January 15, 2024 
12Friday, November 15, 2024 
13Friday, November 15, 2024 
14Tuesday, October 15, 2024Sunday, December 15, 2024
15Tuesday, October 15, 2024Friday, January 31, 2025
16Tuesday, October 15, 2024 
17Tuesday, October 1, 2024Tuesday, December 17, 2024
18Saturday, November 2, 2024 
Message 3 of 12
431 Views
0

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All