How to calculate percentage change from prior date (prior record)?

[juanmobando]

I have a data set what has a column for year and one for year.

I need to create a variance between the values for each year. How can I get this done? I assume that I have to work on a matrix to show the variance for years, but what formula can I use to be able to calculate it?

I tried to see the different options but could not come up to a definitive solution.

Any help on the matter would be highly appreciated.

My data looks like this:

Year	Brand	Sales
2014	A	1200
2014	B	213
2015	A	3234
2015	B	2343

Ideally I want to have it the veriance (see below). I already have been able to define the matrix and have all but the variance unfortunately 😞 . Thank you lots!!!

Brand	2014	2015	% Variance
A	1200	3234	?
B	213	2343	?

[v-jiascu-msft]

Hi @juanmobando,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft]

Hi @juanmobando,

If your data is the same with the sample, you can try this formula. I'm afraid it's hard to create a layout like yours. Maybe you can add two measures. You check it out in this file.

Sales2014 =
CALCULATE ( SUM ( Table1[Sales] ), Table1[Year] = 2014 )

Sales2015 =
CALCULATE ( SUM ( Table1[Sales] ), Table1[Year] = 2015 )

Variance =
VAR thisyear =
    MAX ( 'Table1'[Year] )
VAR lastYear =
    CALCULATE (
        MAX ( 'Table1'[Year] ),
        FILTER ( ALL ( 'Table1'[Year] ), 'Table1'[Year] < MAX ( 'Table1'[Year] ) )
    )
VAR lastYearSales =
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER ( ALL ( 'Table1'[Year] ), Table1[Year] = lastYear )
    )
RETURN
    (
        CALCULATE ( SUM ( Table1[Sales] ), Table1[Year] = thisyear )
            - lastYearSales
    )
        / lastYearSales

Best Regards,

Dale