Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 10 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 "Projects Completed per month" and the other for the count of "Projects 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 | Created Date | Completed Date |
A | 10/18/2024 | 12/2/2024 |
B | 10/3/2024 | 10/30/2024 |
C | 10/3/2024 | 11/25/2024 |
D | 8/20/2024 | 12/31/2024 |
E | 1/3/2025 | |
F | 11/4/2024 | 1/17/2025 |
G | 9/13/2024 | 12/23/2024 |
H | 1/15/2025 | |
I | 10/25/2024 | 1/11/2025 |
J | 1/22/2025 | |
J | 10/12/2024 |
Solved! Go to Solution.
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.
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
@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!
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.
User | Count |
---|---|
131 | |
71 | |
70 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |