Calculating Percent Change

[natasha519]

I have a table with 4 columns as shown below:

I also created 2 calculated columns which a) gives me a 1 for the LatestDate in the dataset (April 2021) and then b) gives me a 1 for the LatestDateMinusOneYear in the dataset (April 2020).

I created a matrix so far and it looks like this (I am using the measures I created (LastestDate and LatestDateMinusOneYear) as the filters.

I would like to calculate the percent change for the ROW for Predicted Value and for the Reported Value for each State. How can I go about this?

Any assistance would be appreciated!

[natasha519]

Thanks! Im not understanding the 'newTable' that you created and the purpose of it?

[Vera_33 · Accepted solution]

Hi @natasha519 

If I understand correctly, you only look at the MaxDate and MaxDateMinus1Year, then you want to put the values and percent change in the same matrix? Here is one way, file attached

I got the dummy data, 

created a new table as the custom column

[natasha519]

It seems to be working, but is there any way to format the column headers as April 2020 and April 2021 instead of m/d/yyyy?

Also how can I format the values to not be decimal and just whole numbers?

[Vera_33]

Hi @natasha519 

Yes, FORMAT the date

[natasha519]

Thank you so much!! Do you mind explaining to me what you did so I can learn for my own self please?

[Vera_33]

Hi @natasha519 

I was creating a custom column for the Columns in Matrix, the use SWITCH to do different calculation (or call different measures) as you wanted to calculate different values in the same matrix...