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

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

Reply
Syndicate_Admin
Administrator
Administrator

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
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1668481073981.png

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

vkalyjmsft_1-1668481266759.png

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.

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1668481073981.png

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

vkalyjmsft_1-1668481266759.png

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
Memorable Member
Memorable Member

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

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:

brrarivdz22_0-1668452961925.png

and create this calendar table:

brrarivdz22_2-1668453055430.png

with the following expression:

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.