How to calculate price variation between periods

[Anonymous]

Hi Everyone,

I need to calculate the variation of prices overtime of a group of products but takinng into account only products that were sold in both periods being compared.

Below you have an illustrative example of the data I am using 

Product	Q1	Q2	Q3	Q4
A	10	11	0	0
B	15	15	12	0
C	0	11	14	14
D	0	0	10	10
E	0	0	10	10
F	0	0	0	10

In this case, I want to calculate the average variation of prices from Q1 to Q2, therefore I need to consider only products A and B as the others were not sold in Q1. If I am comparing Q2 to Q3 it will be then products B and C.

I have unpivot the data as below but I am not finding a way to create a measure that make filter as needed.

Product	Period	Price
A	Q1	10
B	Q1	15
C	Q1	0
...	...	...

Any help will be verymuch appreciated.

[v-piga-msft]

Hi @Anonymous ,

If I understand your requirement that you want to calculate the variation of prices between this beriod and last period?

If so, you could create an index column before unpivoting the column in query editor and then create the calculated column like below.

Column 2 = 
VAR a =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            'Table',
            'Table'[Period] = EARLIER ( 'Table'[Period] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) + 1
        )
    )
RETURN
    'Table'[Price] - a

Here is the output.

If you still need help, please share your desired output so that we could help further on it.

Best Regards,

Cherry

[Anonymous]

Hi @v-piga-msft ,

Thank you for the suggestion but it is not what I am looking for. I am interested to understand the variation of the group of products between quarters.

If we take Q2 vs Q1, we should sum the products that were sold in both periods, which are products A and B. The calculation would be then 26 over 25 that gives +4% variation. When I tried to create the calculation, I did not find a way to filter only product A and B and therefore the result is including product C in Q2 that result in total of 37.

I hope this clarifies my problem.