3 Replies Latest reply on Sep 7, 2018 10:01 AM by Yidou Li

# How to create dynamic YOY % chg by comparing two different measures

Hi everyone,

I'm trying to create a dynamic YOY % chg of our sales forecast vs. the actual sales a year ago by month. And there's also two filters of product and family applied.

The source data looks like this and it goes on to August 2018. I'm trying to create a dynamic YOY % chg that calculates the forecast \$ of table in July-18 vs. the actual sales of table in July-17 to see what % increase the forecast is whenever users select a filter on a certain product. How can I create the calculation? Created one like this "[Forecast \$]/[Actual \$ Sales]-1" but it didn't work.

Thanks!

 Date Fiscal Year Product Family Actual \$ Sales Forecast \$ Jul-17 FY18 table A \$33,250 \$39,900 Jul-17 FY18 paper A \$3,814 \$4,577 Jul-17 FY18 pen B \$29,375 \$35,250 Jul-17 FY18 pin B \$18,374 \$22,049 Jul-17 FY18 chair C \$38,271 \$45,925 Aug-17 FY18 table A \$3,288 \$3,946 Aug-17 FY18 paper A \$3,921 \$4,705
• ###### 1. Re: How to create dynamic YOY % chg by comparing two different measures

Hi, Yidou

Try below calculation. and make sure the combination of filter has data, your original filter chair and family A doesn't have any data, that's why it shows nothing

Hope this helps

ZZ

• ###### 2. Re: How to create dynamic YOY % chg by comparing two different measures

Hi Zhouyi,

Thanks for your response! I'm having a hard time understanding..Can you help explain how to get the "Measure Names" and "Measure Values"? What do they include?

Thank you so much!

• ###### 3. Re: How to create dynamic YOY % chg by comparing two different measures

Sorry, I guess I didn't explain clearly. What I would like to achieve is by comparing the forecast of FY19 (Jul-18 to Jun-19) with the actual \$ sales of FY18 (Jul-17 to Jun-18) instead of comparing them the whole period. I don't know how to get the correct date in the function.

For example, for chair, the desired result would be:

Actual \$ of FY18     4,890,032

Forecast \$ of FY19    1,144,188

FCST vs. Actual % chg    -76.6%