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

Annual Percent Change

[ Edited ]

Hi, this is a sample of my data source:

Geo-LocationTypeRentMonthYear
LondonStudio$1,653Jan-102010
LondonStudio$1,646Feb-102010
LondonStudio$1,575Mar-102010
London1 BDR$1,677Jan-102010
London1 BDR$1,971Feb-102010
London1 BDR$1,920Mar-102010
NYCStudio$1,050Jan-102010
NYCStudio$1,075Feb-102010
NYCStudio$1,150Mar-102010
NYC1 BDR$1,123Jan-102010
NYC1 BDR$1,113Feb-102010
NYC1 BDR$1,246Mar-102010

 

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.

View solution in original post


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:

 

Capture.JPG

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:

Capture.JPG

 

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.