Forum Discussion

h11's avatar
h11
Helper III
1 year ago
Solved

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 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    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's avatar
      h11
      Helper III
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.