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 201...
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.

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.