cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
natasha519
Helper I
Helper I

Calculating Percent Change

I have a table with 4 columns as shown below:

natasha519_0-1638299900717.png

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.

natasha519_1-1638299994740.png

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!

1 ACCEPTED SOLUTION
Vera_33
Super User
Super User

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, 

Vera_33_0-1638321171165.png

created a new table as the custom column

Vera_33_3-1638321272105.png

 

View solution in original post

6 REPLIES 6
natasha519
Helper I
Helper I

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

Vera_33
Super User
Super User

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, 

Vera_33_0-1638321171165.png

created a new table as the custom column

Vera_33_3-1638321272105.png

 

View solution in original post

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?

Hi @natasha519 

 

Yes, FORMAT the date

Vera_33_0-1638325182464.png

 

Vera_33_1-1638325228279.png

 

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

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors