Error: Y-o-Y Calculation

Question

Hi guys,&nbsp;I would to create a table for showing the retail sales value and volume (like below), as well as the % change vs Sep 2021(Y-o-Y). However, I got some issues on Y-o-Y calculation.&nbsp;DateType of OutletsRetail Sales Value($M)Retail Sales value_% Change vs Sep 2021Volume IndexVolume Index_% Change vs Sep 2021Sep 2022All retail outlets&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 28,0720.20%96.7-1.50%&nbsp;For the&nbsp;retail sales value, volume index and volume_% Change vs 2021,&nbsp;they all come from the dataset and their data type are decimal number.&nbsp;&nbsp;&nbsp;&nbsp;Therefore, I just need to set up a DAX formula for calculating the retail sales value vs Sep 2021(Y-o-Y).&nbsp;&nbsp;However, the table showed some errors when I added the&nbsp;Volume Index&nbsp;and % Change vs Sep 2021 for Volume Index&nbsp;field into the table:- Couldn't calculate the % change vs Sep 2021 of retail sales value- There was an extra row with wrong figures.&nbsp;h&nbsp;Could anyone suggest some solutions to solve out the above problems?&nbsp;Thank you.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;S-Learner&nbsp;,
Although you have provided the dax formula and picture with the data, I am not familiar with the measures. How the measures calculate? Could you please provide your pbix file without privacy information and desired output with more details.
&nbsp;
How to Get Your Question Answered Quickly&nbsp;
&nbsp;
Best RegardsCommunity Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

s-learner · Answer

Hi,&nbsp;&nbsp;Thanks for your help! Please find the below pbix file and the raw data.pbix file&nbsp;Raw data&nbsp;&nbsp;Actually, I would like to use Power PI to automate the the data processing and create the below table. The desire output should show the value &amp; volume index, as well as their %Change vs. Sep 2021 (Y-o-Y) and&nbsp;%Change vs. Sep 2018.&nbsp;Please note that the % change of volume should refer to the raw data.&nbsp;&nbsp;&nbsp;Thank you.&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;S-Learner&nbsp;,
I have created a simple sample, please refer to it to see if it helps you.
Create a measure.
Measure = var _1= EDATE(MAX('Retail Sales'[Date]),-12)
var _2=CALCULATE(SUM('Retail Sales'[Value]),FILTER(ALL('Retail Sales'),'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])&amp;&amp;'Retail Sales'[Date]=_1))
return 
(MAX('Retail Sales'[Value])-_2)/_2
&nbsp;

Best RegardsCommunity Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

s-learner · Answer

Hi,&nbsp;&nbsp;Thanks for your great help. I have also tried to calculate the Jan-Sep 2022 Y-o-Y and Jan- Sep 2022 vs Jan-Sep 2018,&nbsp;but I couldn't capture the period Jan- Sep 2018 and&nbsp;Jan-Sep 2022&nbsp;by using the below formula&nbsp;and apply it to your pervious Y-o-Y DAX formula.... Could you please let me any errors did i made for the below DAX formula ?&nbsp; Also, I have attached the&nbsp;pbix file for you.&nbsp;pbix file&nbsp;&nbsp;New formula for Volume index _ Jan-Sep 2022 Y-o-YVolume Index_YTD% Change vs Sep 2021 = var _1= EDATE(MAX('Retail Sales'[Date]) &amp;&amp; 'Retail Sales'[Date] &lt;= MAX('Retail Sales'[Date]),-12)var _2=CALCULATE(SUM('Retail Sales'[Volume Index]),FILTER(ALL('Retail Sales'),'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])&amp;&amp;'Retail Sales'[Date]=_1))return(MAX('Retail Sales'[Volume Index])-_2)/_2&nbsp;My desire output:Major Type of Retail OutletValueValue_% change Jan-Sep 2022 vs. Jan-Sep 2021Volume_% change Jan-Sep 2022 vs. Jan-Sep 2021Value_% change Jan-Sep 2022 vs. Jan-Sep 2018Volume_% change Jan-Sep 2022 vs. Jan-Sep 2018All retail outlets28,072-1.30%-4.20%-29.50%-34.30%&nbsp;Thanks again !&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;S-Learner&nbsp;,
Please have a try.
Create 2 columns first.
month_1 = MONTH('Retail Sales'[Date])Year _1 = YEAR('Retail Sales'[Date])
Then create 4 measures.
2018value = var _now=CALCULATE(SUM('Retail Sales'[Value]),FILTER(ALL('Retail Sales'),'Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])&amp;&amp;'Retail Sales'[month_1]&gt;=1&amp;&amp;'Retail Sales'[month_1]&lt;=9&amp;&amp;'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
var _before=CALCULATE(SUM('Retail Sales'[Value]),FILTER(ALL('Retail Sales'),('Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])-4)&amp;&amp;'Retail Sales'[month_1]&gt;=1&amp;&amp;'Retail Sales'[month_1]&lt;=9&amp;&amp;'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
return
(_now-_before)/_before2018volumn = var _now=CALCULATE(SUM('Retail Sales'[Volume Index]),FILTER(ALL('Retail Sales'),'Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])&amp;&amp;'Retail Sales'[month_1]&gt;=1&amp;&amp;'Retail Sales'[month_1]&lt;=9&amp;&amp;'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
var _before=CALCULATE(SUM('Retail Sales'[Volume Index]),FILTER(ALL('Retail Sales'),('Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])-4)&amp;&amp;'Retail Sales'[month_1]&gt;=1&amp;&amp;'Retail Sales'[month_1]&lt;=9&amp;&amp;'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
return
(_now-_before)/_before2021value = var _now=CALCULATE(SUM('Retail Sales'[Value]),FILTER(ALL('Retail Sales'),'Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])&amp;&amp;'Retail Sales'[month_1]&gt;=1&amp;&amp;'Retail Sales'[month_1]&lt;=9&amp;&amp;'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
var _before=CALCULATE(SUM('Retail Sales'[Value]),FILTER(ALL('Retail Sales'),('Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])-1)&amp;&amp;'Retail Sales'[month_1]&gt;=1&amp;&amp;'Retail Sales'[month_1]&lt;=9&amp;&amp;'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
return
(_now-_before)/_before2021volumn = var _now=CALCULATE(SUM('Retail Sales'[Volume Index]),FILTER(ALL('Retail Sales'),'Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])&amp;&amp;'Retail Sales'[month_1]&gt;=1&amp;&amp;'Retail Sales'[month_1]&lt;=9&amp;&amp;'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
var _before=CALCULATE(SUM('Retail Sales'[Volume Index]),FILTER(ALL('Retail Sales'),('Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])-1)&amp;&amp;'Retail Sales'[month_1]&gt;=1&amp;&amp;'Retail Sales'[month_1]&lt;=9&amp;&amp;'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
return
(_now-_before)/_before

Best RegardsCommunity Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Error: Y-o-Y Calculation

5 Replies

Related content

Calculate Absolute Change Y-o-Y

Y-o-Y Line Chart: Multiple Years

Agregar y/Restar horas de DATETIME y/o Filtro

Calculating Y-O-Y difference up until a certain date

Categorías y o

Date	Type of Outlets	Retail Sales Value($M)	Retail Sales value_% Change vs Sep 2021	Volume Index	Volume Index_% Change vs Sep 2021
Sep 2022	All retail outlets	28,072	0.20%	96.7	-1.50%