Forum Discussion
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 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 Tasks Completed per month" and the other for the "Count of Tasks 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 | Task Name | Task Status | Task Created Date | Task Completed Date |
| A | Task 1 | Completed | 12/11/2024 | 12/13/2024 |
| A | Task 2 | Created | 12/13/2024 | |
| A | Task 3 | Created | 12/13/2024 | |
| A | Task 4 | Completed | 12/16/2024 | 12/23/2024 |
| A | Task 5 | Created | 12/11/2024 | |
| B | Task 1 | Created | 12/12/2024 | |
| B | Task 2 | Completed | 12/12/2024 | 12/12/2024 |
| B | Task 3 | Created | 12/11/2024 | |
| B | Task 4 | Completed | 12/13/2024 | 1/16/2025 |
| B | Task 5 | Created | 12/16/2024 | |
| C | Task 1 | Created | 12/13/2024 | |
| C | Task 2 | Created | 12/12/2024 | |
| C | Task 3 | Completed | 12/16/2024 | 12/16/2024 |
| C | Task 4 | Created | 12/11/2024 | |
| C | Task 5 | Completed | 12/16/2024 | 1/7/2025 |
| D | Task 1 | Completed | 12/11/2024 | 1/3/2025 |
| D | Task 2 | Completed | 12/13/2024 | 12/16/2024 |
| D | Task 3 | Created | 1/16/2025 | |
| D | Task 4 | Created | 1/16/2025 | |
| D | Task 5 | Created | 1/16/2025 |
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 imageThen 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.
4 Replies
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 imageThen 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.
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!
Hi h11,
Perhaps you can also take a look the following link about expand the date range and create new table based on these records if this suitable for your requirement.
Solved: Spread revenue across period based on start and en... - Microsoft Fabric Community
Regards,Xiaoxin Sheng
