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: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
chitti5
Helper I
Helper I

Line graph to show count of projects

Monday

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
252 Views
0
1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support
In response to chitti5

Tuesday

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
76 Views
0
11 REPLIES 11
parry2k
Super User
Super User

Tuesday

@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
102 Views
0
parry2k
Super User
Super User

Tuesday

@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
119 Views
0
parry2k
Super User
Super User

Tuesday

@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
120 Views
0
chitti5
Helper I
Helper I
In response to parry2k

Tuesday

@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
104 Views
0
v-rzhou-msft
Community Support
Community Support
In response to chitti5

Tuesday

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
77 Views
0
parry2k
Super User
Super User

Tuesday

@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
152 Views
0
chitti5
Helper I
Helper I
In response to parry2k

Tuesday

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
121 Views
0
chitti5
Helper I
Helper I

Tuesday

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
164 Views
0
rohit1991
Super User
Super User

Monday

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.



"The goal is to turn data into information, and information into insight." – Carly Fiorina

Need Power BI help? Connect on LinkedIn.

Message 4 of 12
188 Views
0
parry2k
Super User
Super User

Monday

@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
211 Views
0
chitti5
Helper I
Helper I
In response to parry2k

Tuesday

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
164 Views
0

Helpful resources

Announcements
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! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

View All