Forum Discussion

GMS0101's avatar
GMS0101
Frequent Visitor
6 years ago
Solved

Calculate a Percentage Change using Cumulative Data

Hello gurus!

 

I have a situation where I am trying to track day to day percentage change for cumulative time series data in a matrix. My calculation works for the latest date of data but is blank for all previous dates. 

 

 

How do I get this percentage to calculate for each day in my matrix? Here are the two measures I'm using to create this data: 

Measure for Growth in Total Cases:

 

 

 

 

Measure for Total Confirmed:

I'm sure my measures look a bit screwy so any advice anyone could provide would be amazing.

 

Here's a dropbox link to the actual file: https://www.dropbox.com/s/lzz5i0roudeg8f6/Corona%20Stats.pbix?dl=0

 

Thank you so much!!!!

 

Garrett

General Comment
Need Help
Tips and Tricks
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi GMS0101 ,

     

    At first, you should use date in "Calendar" to replace date in "Combined".

    Then I editted these two measures:

    Total Confirmed = SUM ( 'Combined'[Confirmed] )
    Growth in Total Cases =
VAR a =
    CALCULATE (
        [Total Confirmed],
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date] = MAX ( 'Combined'[Date] )
        )
    )
VAR b =
    CALCULATE (
        [Total Confirmed],
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date]
                = MAX ( 'Combined'[Date] ) - 1
        )
    )
RETURN
    DIVIDE ( a - b, b )

    Here is the result.

     

2 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi GMS0101 ,

     

    At first, you should use date in "Calendar" to replace date in "Combined".

    Then I editted these two measures:

    Total Confirmed = SUM ( 'Combined'[Confirmed] )
    Growth in Total Cases =
VAR a =
    CALCULATE (
        [Total Confirmed],
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date] = MAX ( 'Combined'[Date] )
        )
    )
VAR b =
    CALCULATE (
        [Total Confirmed],
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date]
                = MAX ( 'Combined'[Date] ) - 1
        )
    )
RETURN
    DIVIDE ( a - b, b )

    Here is the result.

     

    • GMS0101's avatar
      GMS0101
      Frequent Visitor
      6 years ago

      Beautiful! You are a scholar!! 

       

      I was actually able to get it to work without changing the "Total Confirmed" measure and just implementing your Growth measure. Could you explain to me why the "Total Confirmed" measure should be changed the way you did it?

       

      Thank you soooooooooooo much!!!

       

      -Garrett