Forum Discussion

LakiCG's avatar
LakiCG
Frequent Visitor
8 years ago
Solved

Annual Percent Change

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 20...
Need Help
  • LakiCG's avatar
    LakiCG
    8 years ago

    Hi Anonymous,

    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's avatar
LakiCG
Frequent Visitor
8 years ago

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,

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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