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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
LakiCG
Frequent Visitor
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.