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 ...
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.

     

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