YoY percentage difference

[Anonymous]

Hi all -  trying to calculate the year over year % difference for diff. products. I was able to do it before, when my data was nicely arranged by columns (FY15 Revenue, FY15 Volume, FY15 Profitability; FY16 Revenue, FY16 Volume etc), but now I unpivoted the columns and I'm not able to do that anymore. Any way to work around this issue? 

Thanks so much!

 

[NAOS · Accepted solution]

Hi,

You will need a calendar table that includes a column with the "Year" values formated as in your table.
Once you have that use that column as the axis for your visual.

A couple of mesuares like the following should do the trick.
Revenue = CALCULATE(
                                       SUM(YourTable[Value]), YourTable[Rev.Vol.Profit.GP] = "Revenue")
Revenue previous year = CALCULATE([Revenue], SAMEPERIODLASTYEAR(YourNewCalendarTable[Date]))  /*Where [Date] is the key of YourNewCalendarTable table, in other words, the column with the unique values*/

Revenue%changeYoY =
VAR RevPrevYear = [Revenue previous year]
RETURN
DIVIDE([Revenue] - RevPrevYear, RevPrevYear, Blank()) /*I just like to be explicit about that Blank value, but not really necessary*/

This should work but note this is not how it should be done. It seems to me like you should have a simple column for Revenue values and one other for each of the other attributes in the Rev.Vol.Profit.GP column. 

Let me know how it goes!

[Anonymous]

Thanks, @NAOS, this worked! Also, based on your suggestion, I later managed to create separate columns for each of the attributes (usinf If statements), which makes it way easier for a newbie like me to handle work with the data and create visuals. Thanks again.

[NAOS]

Hey @Anonymous! Im glad it worked.

You could also try using the query editor and pivot the column, instead of using the "if" statements. That will generate one column for each of the different values in the column you pivot. You will have to use another column for the values. In the table posted with the original question, I imagine you will want to pivot the "Attribute" field and use the "Value" column as the values...so basically reverse the operation that created those two columns in the first place.

If you do this then you will have to change your measures, but they will be even more simple 😉

[v-frfei-msft]

Hi @Anonymous,

Could you please share your sample data and excepted result to me?

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft,

This is approximately how my data looks like. I want to be able to show, for instance (in a table visual), that revenue for Product Series A increased 19.3% from FY16 to FY17, but decreased by 6.4% from '17 to '18. I was able to do that before unpivoting, by using a new quick measure-percentage difference. Not sure how to do it now.