Measure for calculating average of past 10 quarters averages

[YC]

Hello! I am new to DAX PBI and I hope somebody can help me with my problem. 

1. My initial data is stated in below "table1": price per material, for each quarter:

Material	Q1 Year1 price	Q2 Year1 price	Q3 Year1 price	Q4 Year1 price	Q1 Year2 price	Q2 Year2 price	Q3 Year2 price	Q4 Year2 price	Q1 Year3 price	Q2 Year3 price
A	1	1			0.5	0.5	0.5	1	1.5	1.5
B	2.5	2.5	2.5	2.5	2.5	2.5	2.5	2.5	2.5	2.5
C	3	2.5			1	1	1	1	1	1
D	2	3			3	2	2	2	2	2

2. I need to calculate the price trends from Q1 Y1 to Q2 Y3 so that I know how many materials had price increase, price decrease or remained flat. I was thinking to do it like this:

 - A) create a measure for each comparison of quarter vs previous quarter to get the % change, as per below table:

Material	Q1 Year1 %	Q2 vs Q1 Year1 %	Q3 vs Q2 Year1 %	Q4vs Q3 Year1 %	Q1 Year2 vs Q4Year1 %	Q2 vs Q1 Year2 %	Q3 vs Q2 Year2 %	Q4 vs Q3 Year2 %	Q1 Year3vs Q4 Year2 %	Q2 vs Q1 Year3 %
A		0%				0%	0%	100%	50%	0%
B		0%	0%	0%	0%	0%	0%	0%	0%	0%
C		-17%				0%	0%	0%	0%	0%
D		50%				-33%	0%	0%	0%	0%

- B) then to create a new measure that will calculate the average of all 10 averages above per each material. And here my problem appeared, cause I have tried to do this measure like this (below it's just an example for 2 measures, but I want to add all above comparison measures from step A):

calculate(averagex ('table1', union(row(''value",measure[Q2 vs Q1 Year1 %]),row("value",measure[ Q3 vs Q2Year1 %]),groupby('table1",[Material]))

I should get to something like this:

Material	Total average (Q1 Y1 - Q2 Y3)
A	25%
B	0%
C	-3%
D	3%

The problem is that this measure is not working...I don't get any error, but still it doesn't return any value.

 Can somebody help me understand what am I doing wrong? Or maybe there is another method to obtain the result I want?

Thank you!

[Jihwan_Kim · Accepted solution]

Hi, @YC 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

All steps are numbered in front of each measure.

I suggest having data model like below.

https://www.dropbox.com/s/owhwzxwxm5gfjtz/YC.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[YC]

Thank you! I managed to solve my problem with your help!

[amitchandak]

@YC , if the first table is your data format, I would advise to unpivot that

https://radacad.com/pivot-and-unpivot-with-power-bi

Create a qtr year table with YYYYQQ as one of the columns (say date)

then you can rank on yyyy qq

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

You can create measures like
This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

Last 10 Qtr = CALCULATE(Average('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]>=max('Date'[Qtr Rank])-10 && 'Date'[Qtr Rank]=<max('Date'[Qtr Rank]) ))