Annual Percent Change

[LakiCG]

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

[LakiCG]

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,

[v-shex-msft]

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

[LakiCG · Accepted solution]

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.

[LakiCG]

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,

[v-shex-msft]

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