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

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,

 

  • 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.