Line Chart - Project Created vs Project Completed

[h11]

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

[Ritaf1983 · Accepted solution]

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]

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

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.

[Anonymous]

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