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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
h11
Helper III
Helper III

Projects Created Vs Projects Completed

Hello All,

 

My team has requested a line chart comparing "Project Completed per Month" vs "Projects Created per Month." I have data for approximately 250 projects and have attached a sample with 10 projects for reference. Could you please provide the formula to create a line graph where the X-axis represents months, and the Y-axis has two lines: one for the count of "Projects Completed per month" and the other for the count of "Projects Created per month"?

 

I request to attach the Power BI file if possible that you worked on using my data as a reference. Thank you in advance.

 

Project NameCreated DateCompleted Date
A10/18/202412/2/2024
B10/3/202410/30/2024
C10/3/202411/25/2024
D8/20/202412/31/2024
E1/3/2025 
F11/4/20241/17/2025
G9/13/202412/23/2024
H1/15/2025 
I10/25/20241/11/2025
J1/22/2025 
J10/12/2024 
1 ACCEPTED SOLUTION

Thanks for the reply from Sahir_Maharaj , please allow me to provide another insight:

Hi, @h11 
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

1.First, you need to create a new date table that includes all dates,Please ensure that this calculated table does not establish any relationships with the original data:

date = 
    CALENDAR(
        MINX(
            {
                MIN('Table'[Completed Date]),
                MIN('Table'[Created Date])
            },
            [Value]
        ),
        MAXX(
            {
                MAX('Table'[Completed Date]),
                MAX('Table'[Created Date])
            },
            [Value]
        )
    )

vlinyulumsft_0-1737708837882.png

2.Secondly, create the following two measures:

Projects Completed = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Completed Date] ) = MONTH ( MAX ( 'date'[Date] ) )
            && YEAR ( 'Table'[Completed Date] ) = YEAR ( MAX ( 'date'[Date] ) )
    )
)
Projects Created = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Created Date] ) = MONTH ( MAX ( 'date'[Date] ) )
            && YEAR ( 'Table'[Created Date] ) = YEAR ( MAX ( 'date'[Date] ) )
    )
)

3.Next, modify the format of the date: 

vlinyulumsft_1-1737708881126.png

4.Here's my final result, which I hope meets your requirements.

vlinyulumsft_2-1737708881127.png

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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

3 REPLIES 3
Sahir_Maharaj
Super User
Super User

Hello @h11,

 

Can you please try this approach:

Projects Created vs Completed = 
VAR CreatedProjects = 
    CALCULATE(
        COUNTROWS('Projects'),
        FILTER(
            'Projects',
            'Projects'[Created Date] >= MIN('Date Table'[Date]) &&
            'Projects'[Created Date] <= MAX('Date Table'[Date])
        )
    )
VAR CompletedProjects = 
    CALCULATE(
        COUNTROWS('Projects'),
        FILTER(
            'Projects',
            'Projects'[Completed Date] >= MIN('Date Table'[Date]) &&
            'Projects'[Completed Date] <= MAX('Date Table'[Date])
        )
    )
RETURN
    CreatedProjects + 0 + CompletedProjects

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

@Sahir_Maharaj : Hi Sir,

 

Thank you for trying this approach. Unfortunately this approach didn't work out for my data. I want 2 lines in the line chart. One line representing completed projects and the other line representing created projects in the same chart. 

 

I used the formula provided above and got the graph below.

 

h11_0-1737584869118.png

Please help me by working on the rough data that I provided and share your worked power bi file or screenshots if possible.

 

Thank you!

Thanks for the reply from Sahir_Maharaj , please allow me to provide another insight:

Hi, @h11 
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

1.First, you need to create a new date table that includes all dates,Please ensure that this calculated table does not establish any relationships with the original data:

date = 
    CALENDAR(
        MINX(
            {
                MIN('Table'[Completed Date]),
                MIN('Table'[Created Date])
            },
            [Value]
        ),
        MAXX(
            {
                MAX('Table'[Completed Date]),
                MAX('Table'[Created Date])
            },
            [Value]
        )
    )

vlinyulumsft_0-1737708837882.png

2.Secondly, create the following two measures:

Projects Completed = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Completed Date] ) = MONTH ( MAX ( 'date'[Date] ) )
            && YEAR ( 'Table'[Completed Date] ) = YEAR ( MAX ( 'date'[Date] ) )
    )
)
Projects Created = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Created Date] ) = MONTH ( MAX ( 'date'[Date] ) )
            && YEAR ( 'Table'[Created Date] ) = YEAR ( MAX ( 'date'[Date] ) )
    )
)

3.Next, modify the format of the date: 

vlinyulumsft_1-1737708881126.png

4.Here's my final result, which I hope meets your requirements.

vlinyulumsft_2-1737708881127.png

Please find the attached pbix relevant to the case.

 

Best Regards,

Leroy Lu

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

Helpful resources

Announcements
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!

Jan NL Carousel

Fabric Community Update - January 2025

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