Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Month by Month Running Total Graph

I've been struggling at this for a while, so I'd really appreciate some help on this.

 

I'm trying to visualize the progress on the projects in the dataset (will explain this further). Here is the data I'm working with:

 

 

I'm trying to create a 100% stacked graph where the x-axis is 'Months' - and each month shows all 10 projects and displays the percentage of 'In Progress', 'Completed', and 'Abandoned' projects. As the months go by, the percentages shift as more projects are put in 'Completed'.

 

I wanted the graph set up so that in January, a project is ONLY labeled as 'Completed' if the completion date is before or in January 2021 - hence the 'In Progress' category contains rows either labelled as 'In Progress' or labelled as 'Completed' but the completion date is after January. Then, in each month, a project is removed from the 'In Progress' category and is instead displayed as 'Completed' if its completion date falls under that month.

 

So according to the logic above:

In January, 2 projects are Completed, 7 are In Progress, and 1 is Abandoned

In March, 3 projects are Completed, 6 are In Progress, and 1 is Abandoned

In May, 4 projects are Completed, 5 are In Progress, and 1 is Abandoned

In July, 5 projects are Completed, 4 are In Progress, and 1 is Abandoned

 

I've looked over posts on this forum involving a running total and tried using a version of this formula:

  CALCULATE(
        COUNTA(Sheet1[Qty]),FILTER(ALL(Sheet1),Sheet1[Date]<=MAX(Sheet1[Date])))

 

But I face 2 issues:

1) New projects are simply added to the 'Completed' category, but not removed from the 'In Progress for each month (the overall number of projects across all months need to stay the same)

2) For some reason, the formula only works when using a 'Month Index' as the date - meaning the X-axis are numbered from 1-12, and when I try to use a 'Month' column that is sorted by the 'Month Index', the graph stops changing from month-to-month.

 

I know this is a little confusing, so let me know if clarification is needed. I would greatly appreciate your help on this. Thank you so much in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ，

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Create a date dimension table, no need to create any relationship with your fact table

    2. Create 3 measures as below to get the count of projects with in progress, completed and abandoned status

    Completed = 
CALCULATE (
    DISTINCTCOUNT ( 'Sheet1'[Peoject ID] ),
    FILTER (
        ALLSELECTED ( 'Sheet1' ),
        'Sheet1'[Status] = "Completed"
            && 'Sheet1'[Completion Date] <= SELECTEDVALUE ( 'Date'[Date] )
    )
)
    In progress = 
CALCULATE (
    DISTINCTCOUNT ( 'Sheet1'[Peoject ID] ),
    FILTER (
        'Sheet1',
        ISBLANK ( 'Sheet1'[Completion Date] )
            || 'Sheet1'[Completion Date] >= SELECTEDVALUE ( 'Date'[Date] )
    )
)
    Abandoned = 
CALCULATE (
    DISTINCTCOUNT ( 'Sheet1'[Peoject ID] ),
    FILTER (
        'Sheet1',
        'Sheet1'[Status] = "Abandoned"
            && 'Sheet1'[Completion Date] <= SELECTEDVALUE ( 'Date'[Date] )
    )
)

    Best Regards

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ，

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Create a date dimension table, no need to create any relationship with your fact table

    2. Create 3 measures as below to get the count of projects with in progress, completed and abandoned status

    Completed = 
CALCULATE (
    DISTINCTCOUNT ( 'Sheet1'[Peoject ID] ),
    FILTER (
        ALLSELECTED ( 'Sheet1' ),
        'Sheet1'[Status] = "Completed"
            && 'Sheet1'[Completion Date] <= SELECTEDVALUE ( 'Date'[Date] )
    )
)
    In progress = 
CALCULATE (
    DISTINCTCOUNT ( 'Sheet1'[Peoject ID] ),
    FILTER (
        'Sheet1',
        ISBLANK ( 'Sheet1'[Completion Date] )
            || 'Sheet1'[Completion Date] >= SELECTEDVALUE ( 'Date'[Date] )
    )
)
    Abandoned = 
CALCULATE (
    DISTINCTCOUNT ( 'Sheet1'[Peoject ID] ),
    FILTER (
        'Sheet1',
        'Sheet1'[Status] = "Abandoned"
            && 'Sheet1'[Completion Date] <= SELECTEDVALUE ( 'Date'[Date] )
    )
)

    Best Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you so much for the detailed response. I was able to get it to work. Can't thank you enough for this!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you so much - I reverse engineered what you did in the file to my larger dataset and got it to work after weeks of struggling. Thanks again!