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

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.