Graph of simultaneous projects

[Syndicate_Admin]

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.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yanjiang-msft · Accepted solution]

Hi @Syndicate_Admin ,

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.

[Shaurya]

Hi @Syndicate_Admin,

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

[Syndicate_Admin]

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.