Forum Discussion

h11's avatar
h11
Helper III
1 year ago
Solved

Line Chart - Project Created vs Project Completed

Hello All,   My team has requested a line chart comparing "Tasks Completed per Month" and "Tasks Created per Month." I have data for approximately 250 projects and have attached a sample with 4 pro...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    1 year ago

    Hi h11 

    Assuming you have a calendar table,
    you need to create two relationships between it and the tasks table:

    An active relationship with the task creation date.
    An inactive relationship with the task completion date.
    As shown in the attached image

    Then you can create 2 measures :

    Created tasks = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Task Status]="Created"))
    Completed tasks = CALCULATE(COUNTROWS('Table'),USERELATIONSHIP('Calendar'[Date],'Table'[Task Completed Date]),'Table'[Task Status]="Completed")
    Put the measures + month column from calendar table on the graph :

    The pbix is attached

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

h11's avatar
h11
Helper III
1 year ago

Ritaf1983 : Thank you so much for attaching the pbi file. This saved my time. I appreciate it!

 

Could you provide a measure that excludes the task status? I want to create a line graph using only the months and the created and completed dates, without including the task status into the measure.

 

Thank you in advance!

Ritaf1983's avatar
Ritaf1983
Super User
1 year ago

Hi h11 
You can modify the formulas to :

Created tasks = COUNTROWS('Table')
Completed tasks = CALCULATE(COUNTROWS('Table'),USERELATIONSHIP('Calendar'[Date],'Table'[Task Completed Date]),'Table'[Task Completed Date]<>BLANK())
The updated PBIX is attached

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