Forum Discussion
Dynamic Price Index based on Quarter
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 201...
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.
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
Community Support
Community SupportAnonymous ,
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.