## Desktop

Frequent Visitor
Posts: 12
Registered: ‎04-05-2017

# Annual Percent Change

[ Edited ]

Hi, this is a sample of my data source:

 Geo-Location Type Rent Month Year London Studio \$1,653 Jan-10 2010 London Studio \$1,646 Feb-10 2010 London Studio \$1,575 Mar-10 2010 London 1 BDR \$1,677 Jan-10 2010 London 1 BDR \$1,971 Feb-10 2010 London 1 BDR \$1,920 Mar-10 2010 NYC Studio \$1,050 Jan-10 2010 NYC Studio \$1,075 Feb-10 2010 NYC Studio \$1,150 Mar-10 2010 NYC 1 BDR \$1,123 Jan-10 2010 NYC 1 BDR \$1,113 Feb-10 2010 NYC 1 BDR \$1,246 Mar-10 2010

I want to get an annual avarege change (%) but for some reason the result isn't quit accuarate:

%StudioRent = CALCULATE(IF(HASONEVALUE('Rental Trends'[Year]), DIVIDE(SUM('Rental Trends'[Rent]), CALCULATE(SUM('Rental Trends'[Rent]), 'Rental Trends'[Year] = FORMAT(VALUES('Rental Trends'[Year]) - 1, BLANK())))) - 1,'Rental Trends'[Type] = "Studio")

Does someone know how to achieve this?
Thanks

Accepted Solutions
Frequent Visitor
Posts: 12
Registered: ‎04-05-2017

## Re: Annual Percent Change

[ Edited ]

Hi @v-shex-msft,

Based on your answer I re-write formula and got what I needed (this refers to the previous year; the formula for the current year remains the same):

AVG Rent Studio PY =
IF (
HASONEVALUE ( 'Rental Trend'[Year] ),
CALCULATE (
AVERAGE ( 'Rental Trend'[Rent] ),
FILTER (
ALL( 'Rental Trend' ),
'Rental Trend'[Year]
= MAX( 'Rental Trend'[Year] ) - 1
),
VALUES ( 'Rental Trend'[Type] )
)
)

This piece of formula filtered a previous year:

PY = MAX( 'Rental Trend'[Year] ) - 1

Year to year change:

AnnualChange = 1 - DIVIDE([AVG Rent PY], [AVG Rent CY])

Thank you.

All Replies
Frequent Visitor
Posts: 12
Registered: ‎04-05-2017

## Re: Annual Percent Change

Let me ask this in another way. Using the above table I calculated an average rent using this formula (for "Studio" type):

AVGRentStudio = CALCULATE(AVERAGE('Rental Trend'[Rent]),'Rental Trend'[Type] = "Studio")

And I presented the average values per year in tabular form:

Now, I would like to show the percentage change (compared to the previous year). Can anyone help to achieve this?

Thanks,

Highlighted
Community Support Team
Posts: 6,749
Registered: ‎08-14-2016

## Re: Annual Percent Change

Hi @LakiCG,

You can try to use below measure if it works for your scenario:

Change % with Prev Year =
VAR _Curr =
CALCULATE (
AVERAGE ( Table1[Rent] ),
VALUES ( Table1[Geo-Location] ),
VALUES ( Table1[Year] )
)
VAR _prev =
CALCULATE (
AVERAGE ( Table1[Rent] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Year]
= SELECTEDVALUE ( Table1[Year] ) - 1
),
VALUES ( Table1[Geo-Location] )
)
RETURN
DIVIDE ( _Curr - _prev, _prev, -1 )

Regards,

Xiaoxin Sheng

Frequent Visitor
Posts: 12
Registered: ‎04-05-2017

## Re: Annual Percent Change

After some research around I got an idea and did this finaly.

Measure 1 (for previous year):

AVG Rent Studio PY = CALCULATE(IF(HASONEVALUE('Rental Trend'[Year]), CALCULATE(AVERAGE('Rental Trend'[Rent]), 'Rental Trend'[Year] = FORMAT(VALUES('Rental Trend'[Year]) - 1, BLANK()), 'Rental Trend'[Type] = "Studio")))

Measure 2 (selected year):

AVG Rent Studio CY = IF(HASONEVALUE('Rental Trend'[Year]), CALCULATE(AVERAGE('Rental Trend'[Rent]), 'Rental Trend'[Type] = "Studio"))

Measure 3 (year to year change):

Y/Y Rent Change = 1 - DIVIDE([AVG Rent Studio PY],[AVG Rent Studio CY])

And the result looks like this:

Can I rewrite this solution and make it dynamic (showing year-to-year change based on "Type" selection (Studio, 1BDR, 2BDR)?

Thanks,

Community Support Team
Posts: 6,749
Registered: ‎08-14-2016

## Re: Annual Percent Change

Hi @LakiCG,

You can try to use below formula:

AVG Rent Studio PY =
IF (
HASONEVALUE ( 'Rental Trend'[Year] ),
CALCULATE (
AVERAGE ( 'Rental Trend'[Rent] ),
FILTER (
ALLSELECTED ( 'Rental Trend' ),
'Rental Trend'[Year]
= SELECTEDVALUE ( 'Rental Trend'[Year] ) - 1
),
VALUES ( 'Rental Trend'[Type] )
)
)

AVG Rent Studio CY =
IF (
HASONEVALUE ( 'Rental Trend'[Year] ),
CALCULATE ( AVERAGE ( 'Rental Trend'[Rent] ), VALUES ( 'Rental Trend'[Type] ) )
)

Regards,

Xiaoxin Sheng

Frequent Visitor
Posts: 12
Registered: ‎04-05-2017

## Re: Annual Percent Change

[ Edited ]

Hi @v-shex-msft,

Based on your answer I re-write formula and got what I needed (this refers to the previous year; the formula for the current year remains the same):

AVG Rent Studio PY =
IF (
HASONEVALUE ( 'Rental Trend'[Year] ),
CALCULATE (
AVERAGE ( 'Rental Trend'[Rent] ),
FILTER (
ALL( 'Rental Trend' ),
'Rental Trend'[Year]
= MAX( 'Rental Trend'[Year] ) - 1
),
VALUES ( 'Rental Trend'[Type] )
)
)

This piece of formula filtered a previous year:

PY = MAX( 'Rental Trend'[Year] ) - 1

Year to year change:

AnnualChange = 1 - DIVIDE([AVG Rent PY], [AVG Rent CY])

Thank you.