cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

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

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 ?
2 REPLIES 2
Microsoft

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

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft

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

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

## Helpful resources

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors