Error: Y-o-Y Calculation

[S-Learner]

Hi guys,

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.

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%

For the retail sales value, volume index and volume_% Change vs 2021, they all come from the dataset and their data type are decimal number.  

Therefore, I just need to set up a DAX formula for calculating the retail sales value vs Sep 2021(Y-o-Y).

However, the table showed some errors when I added the Volume Index and % Change vs Sep 2021 for Volume Index field into the table:

- Couldn't calculate the % change vs Sep 2021 of retail sales value

- There was an extra row with wrong figures.

h

Could anyone suggest some solutions to solve out the above problems?

Thank you.

[v-rongtiep-msft]

Hi @S-Learner ,

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.

How to Get Your Question Answered Quickly 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[S-Learner]

Hi, 

Thanks for your help! Please find the below pbix file and the raw data.

pbix file 

Raw data 

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 & volume index, as well as their %Change vs. Sep 2021 (Y-o-Y) and %Change vs. Sep 2018. Please note that the % change of volume should refer to the raw data. 

Thank you.

[v-rongtiep-msft]

Hi @S-Learner ,

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])&&'Retail Sales'[Date]=_1))
return 
(MAX('Retail Sales'[Value])-_2)/_2

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[S-Learner]

Hi, 

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, but I couldn't capture the period Jan- Sep 2018 and Jan-Sep 2022 by using the below formula 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 ?  Also, I have attached the pbix file for you.

pbix file 

New formula for Volume index _ Jan-Sep 2022 Y-o-Y

Volume Index_YTD% Change vs Sep 2021 = var _1= EDATE(MAX('Retail Sales'[Date]) && 'Retail Sales'[Date] <= 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])&&'Retail Sales'[Date]=_1))

return

(MAX('Retail Sales'[Volume Index])-_2)/_2

My desire output:

Major Type of Retail Outlet	Value	Value_% change Jan-Sep 2022 vs. Jan-Sep 2021	Volume_% change Jan-Sep 2022 vs. Jan-Sep 2021	Value_% change Jan-Sep 2022 vs. Jan-Sep 2018	Volume_% change Jan-Sep 2022 vs. Jan-Sep 2018
All retail outlets	28,072	-1.30%	-4.20%	-29.50%	-34.30%

Thanks again !

[v-rongtiep-msft]

Hi @S-Learner ,

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])&&'Retail Sales'[month_1]>=1&&'Retail Sales'[month_1]<=9&&'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)&&'Retail Sales'[month_1]>=1&&'Retail Sales'[month_1]<=9&&'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
return
(_now-_before)/_before

2018volumn = var _now=CALCULATE(SUM('Retail Sales'[Volume Index]),FILTER(ALL('Retail Sales'),'Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])&&'Retail Sales'[month_1]>=1&&'Retail Sales'[month_1]<=9&&'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)&&'Retail Sales'[month_1]>=1&&'Retail Sales'[month_1]<=9&&'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
return
(_now-_before)/_before

2021value = var _now=CALCULATE(SUM('Retail Sales'[Value]),FILTER(ALL('Retail Sales'),'Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])&&'Retail Sales'[month_1]>=1&&'Retail Sales'[month_1]<=9&&'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)&&'Retail Sales'[month_1]>=1&&'Retail Sales'[month_1]<=9&&'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
return
(_now-_before)/_before

2021volumn = var _now=CALCULATE(SUM('Retail Sales'[Volume Index]),FILTER(ALL('Retail Sales'),'Retail Sales'[Year _1]=SELECTEDVALUE('Retail Sales'[Year _1])&&'Retail Sales'[month_1]>=1&&'Retail Sales'[month_1]<=9&&'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)&&'Retail Sales'[month_1]>=1&&'Retail Sales'[month_1]<=9&&'Retail Sales'[Type of Outlets]=SELECTEDVALUE('Retail Sales'[Type of Outlets])))
return
(_now-_before)/_before

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.