Annual Percent Change in Power BI

0
votes

I am trying to calculate an annual percentage change in Power BI using DAX and need help here. This is my data source in Power BI: enter image description here

Then I calculated an average rent using this formula (for "studio" type):

AVGRentStudio = CALCULATE(AVERAGE('Rental Trend'[Rent]),'Rental Trend'[Type] = "Studio")

And I got a tabular presentation of average values: enter image description here

Now I would like to calculate an annual percentage change (to the previous year).

This is what I tried, but the result isn't accurate.

RentChange =
CALCULATE (
    IF (
        HASONEVALUE ( 'Rental Trend'[Year] ),
        DIVIDE (
            SUM ( 'Rental Trend'[Rent] ),
            CALCULATE (
                SUM ( 'Rental Trend'[Rent] ),
                'Rental Trend'[Year]
                    = FORMAT ( VALUES ( 'Rental Trend'[Year] ) - 1, BLANK () )
            )
        )
    )
        - 1,
    'Rental Trend'[Type] = "Studio"

Can anyone help how to calculate the annual percentage change?

Thanks

2
powerbidaxpowerbi-desktop

2 Answers

2
votes

One way to do it:

Measure 1:

Average Rent Studio =
CALCULATE ( AVERAGE ( 'Rental Trend'[Rent] ), 'Rental Trend'[Type] = "Studio" )

Measure 2:

Average Rent Studio Previous Year =
VAR Current_Year = MAX ( 'Rental Trend'[Year] )
RETURN
    CALCULATE ( [Average Rent Studio], 'Rental Trend'[Year] = Current_Year - 1 )

Measure 3:

Average Rent Studio Y/Y Change = 
DIVIDE([Average Rent Studio], [Average Rent Studio Previous Year])

How it works: Measures 1 and 3 are simple. in Measure 2, you first need to capture current year (it's the max year in a given filter context) and store it in a variable. Then recalculate studio rent using 1 year before the saved year.

1
votes

@Rado I got an idea based on your answer.

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):

VG 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])

Can I make this solution dynamic in order to show year-to-year change based on "Type" selection - Studio, 1BDR, 2BDR?

Thanks