cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## Graph of simultaneous projects

Hello good day, I have a database of projects with their start and end dates, the database is very simple consists of the name of the project, planned start and end date.

I would like to count how many projects I will be doing throughout the year simultaneously. Example:

Project 1 ; Start date: 21/03/2022 ; End date: 24/05/2022

Project 2 ; Start date: 10/04/2022 ; End date: 28/08/2022

Project 3 ; Start date: 10/05/2022 ; End date: 15/07/2022

In March I will be doing: 1 project

In April I will be doing: 2 projects (because project 1 has not yet concluded)

In May I will be doing: 3 projects

In June I will be doing: 2 projects (project 1 already concluded)

In July I will be doing: 2 projects

In August I will be doing: 1 project

Once I have counted how many projects I will be doing per month I like to use a column chart that helps me visualize the peaks of work of the projects.

I appreciate an answer that will help me to carry out what has been raised.

Best regards.

1 ACCEPTED SOLUTION
Community Support

According to your description, here's my solution.

1.In the Calendar table, create a calculated column.

``Month = FORMAT([Date],"MMM")``

2.Create a measure.

``````Count =
SUMX (
'Table',
IF (
MONTH ( 'Table'[Start date] ) <= MONTH ( MAX ( 'Calendar'[Date] ) )
&& MONTH ( 'Table'[End date] ) >= MONTH ( MAX ( 'Calendar'[Date] ) ),
1,
0
)
)
``````

Then put the new column and measure in a bar chart, get the result.

Note:

1. The data table and Calendar table are not related.

2. To sort the chart X-axis in correct month order, I create a MonthNum column and sort the Month column by MonthNum column.

``MonthNum = MONTH([Date])``

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

4 REPLIES 4
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

According to your description, here's my solution.

1.In the Calendar table, create a calculated column.

``Month = FORMAT([Date],"MMM")``

2.Create a measure.

``````Count =
SUMX (
'Table',
IF (
MONTH ( 'Table'[Start date] ) <= MONTH ( MAX ( 'Calendar'[Date] ) )
&& MONTH ( 'Table'[End date] ) >= MONTH ( MAX ( 'Calendar'[Date] ) ),
1,
0
)
)
``````

Then put the new column and measure in a bar chart, get the result.

Note:

1. The data table and Calendar table are not related.

2. To sort the chart X-axis in correct month order, I create a MonthNum column and sort the Month column by MonthNum column.

``MonthNum = MONTH([Date])``

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

Memorable Member

Create a new table with a column that has all the months from your original table and join them. Then use:

``Count = SUMX('New Table',IF('New Table'[Month]>='Table'[Start Month] && 'New Table'[Month]<='Table'[End Month],1,0))``

Then you can use this column in a column chart with the month field as your X-Axis.

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

Hello, at the time of wanting to use the formula does not allow me to use the original table even with the data already related.

This is my data table:

and create this calendar table:

with the following expression:

Calendar = CALENDAR(MIN('Table 1'[Start date]),.MAX('Table 1'[End date]))
Hence, what would follow?
Stay tuned.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors