Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
h11
Helper III
Helper III

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 NameTask NameTask StatusTask Created DateTask Completed Date
ATask 1Completed12/11/202412/13/2024
ATask 2Created12/13/2024 
ATask 3Created12/13/2024 
ATask 4Completed12/16/202412/23/2024
ATask 5Created12/11/2024 
BTask 1Created12/12/2024 
BTask 2Completed12/12/202412/12/2024
BTask 3Created12/11/2024 
BTask 4Completed12/13/20241/16/2025
BTask 5Created12/16/2024 
CTask 1Created12/13/2024 
CTask 2Created12/12/2024 
CTask 3Completed12/16/202412/16/2024
CTask 4Created12/11/2024 
CTask 5Completed12/16/20241/7/2025
DTask 1Completed12/11/20241/3/2025
DTask 2Completed12/13/202412/16/2024
DTask 3Created1/16/2025 
DTask 4Created1/16/2025 
DTask 5Created1/16/2025 

 

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

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

Ritaf1983_0-1737513310845.png

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 :
Ritaf1983_1-1737514456428.png

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.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

4 REPLIES 4
Ritaf1983
Super User
Super User

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

Ritaf1983_0-1737513310845.png

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 :
Ritaf1983_1-1737514456428.png

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.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.