Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 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:

ProductPeriodProduct Cost
A2018 Q11.0
A2018 Q21.20
A2018 Q31.21
A2018 Q41.22
A2019 Q11.19
A2019 Q21.18
A2019 Q31.19
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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.

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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?

       

      ProductPeriodProduct Cost
      A2018 Q11.0
      A2018 Q21.20
      A2018 Q31.21
      A2018 Q41.22
      A2019 Q11.19
      A2019 Q21.18
      A2019 Q31.19
      B2018 Q12.1
      B2018 Q22.2
      B2018 Q32.1
      B2018 Q42.0
      B2019 Q12.2
      B2019 Q22.3
      B2019 Q32.2

       

      • v-yuta-msft's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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.