Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LakiCG
Frequent Visitor

Annual Percent Change

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

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

View solution in original post

5 REPLIES 5
LakiCG
Frequent Visitor

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,

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
Frequent Visitor

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,

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.