Projects Created Vs Projects Completed

[h11]

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 Name	Created Date	Completed Date
A	10/18/2024	12/2/2024
B	10/3/2024	10/30/2024
C	10/3/2024	11/25/2024
D	8/20/2024	12/31/2024
E	1/3/2025
F	11/4/2024	1/17/2025
G	9/13/2024	12/23/2024
H	1/15/2025
I	10/25/2024	1/11/2025
J	1/22/2025
J	10/12/2024

[Sahir_Maharaj]

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

[h11]

@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.

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!

[Anonymous · 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]
        )
    )

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: 

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

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.