Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

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.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

4 Replies

  • Shaurya's avatar
    Shaurya
    Memorable Member
    3 years ago

    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's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      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.
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    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.