Dynamic Price Index based on Quarter

[Anonymous]

Hi, 

I have a particular problem whereby i need to show a dynamic price index based on the latest quarter. 

Back ground:

Now we are in 2019 Q2. I have the historical product cost from 2018 Q1 till 2019 Q2. 2019 Q3 is a forecast of how the product cost will be in the following quarter. I need to be able to look back at 1 year ago, and build a price index that starts from 2018 Q2 till 2019 Q3.

This needs to be updated automatically when we are in 2019 Q3 - that is the price index takes 2018 Q3 as base

How can i create a dynamic price index where the base quarter keeps changing as we progress to another quarter?

Price Trend Table:

Product	Period	Product Cost
A	2018 Q1	1.0
A	2018 Q2	1.20
A	2018 Q3	1.21
A	2018 Q4	1.22
A	2019 Q1	1.19
A	2019 Q2	1.18
A	2019 Q3	1.19

[v-yuta-msft]

@Anonymous ,

Firstly, in query editor, use "split columns" to modify the table like below:

 

Then create a measure using DAX below:

Result = 
VAR Current_Year = MAX('Table'[Year])
VAR Current_Quarter = MAX('Table'[Quarter])
VAR Start_Year = Current_Year - 1
VAR Start_Quarter = Current_Quarter - 1
RETURN
CALCULATE(AVERAGE('Table'[Product Cost]), FILTER(ALL('Table'), 'Table'[Year] >= Start_Year && 'Table'[Year] <= Current_Year && 'Table'[Quarter] >= Start_Quarter && 'Table'[Quarter] <= Current_Quarter))

 

Community Support Team _ Jimmy Tao

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

[Anonymous]

Hi Jimmy,

Thanks for the suggestion.

This seems to work when there is only one product. It now takes the average of all the products. How about when there are multiple products?

Product	Period	Product Cost
A	2018 Q1	1.0
A	2018 Q2	1.20
A	2018 Q3	1.21
A	2018 Q4	1.22
A	2019 Q1	1.19
A	2019 Q2	1.18
A	2019 Q3	1.19
B	2018 Q1	2.1
B	2018 Q2	2.2
B	2018 Q3	2.1
B	2018 Q4	2.0
B	2019 Q1	2.2
B	2019 Q2	2.3
B	2019 Q3	2.2

[v-yuta-msft · Accepted solution]

@Anonymous ,

Modify the measure as below:

Result = 
VAR Current_Year = MAX('Table'[Year])
VAR Current_Quarter = MAX('Table'[Quarter])
VAR Start_Year = Current_Year - 1
VAR Start_Quarter = Current_Quarter - 1
RETURN
CALCULATE(AVERAGE('Table'[Product Cost]), FILTER(ALLEXCEPT('Table', 'Table[Product]'), 'Table'[Year] >= Start_Year && 'Table'[Year] <= Current_Year && 'Table'[Quarter] >= Start_Quarter && 'Table'[Quarter] <= Current_Quarter))

Community Support Team _ Jimmy Tao

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