Comparing Change in data in 2 time periods

[Anonymous]

I am working on Real Estate data and i have 10 years data. i have to generate 2 KPIs

fist is the AVERAGE AREA SIZE OF TRANSACTED LAND and you may guessed it the average was calculated as follows

AVG Area size of transacted land = sum of Area / no. of transactions

for thsi i created new measure using dax formula as follows:

AVARAGE AREA SIZE OF TRANSACTED LAND = SUM('COMPANY'[AREA]) / DISTINCTCOUNT('COMPANY'[DEED NO])

now the hard part which i need help. the second KPI is Changes in Average area size between time period. and that is compairing the average area transactions for 2 years or any 2 time periods. 

the math formula is :

Changes in Average area size between time period = (Present Average Area size per meter) - (Past Period Average Area size per meter) / (Present Average Area size per meter)

how can i do this with dax.

[v-yuta-msft · Accepted solution]

@Anonymous ,

Suppose past Period is 2 years ago and assume no other tables. You may create a measure using DAX like pattern below:

Changes in Average area size between time period =
VAR Current_Year =
    YEAR ( COMPANY[Date] )
VAR Present_Average_Area_size_per_meter =
    CALCULATE (
        [AVARAGE AREA SIZE OF TRANSACTED LAND],
        FILTER (
            ALLEXCEPT ( COMPANY, COMPANY[Meter] ),
            YEAR ( COMPANY[Date] ) <= Current_Year
                && YEAR ( COMPANY[Date] ) >= Current_Year - 1
        )
    )
VAR Past_Period_Average_Area_size_per_meter =
    CALCULATE (
        [AVARAGE AREA SIZE OF TRANSACTED LAND],
        FILTER (
            ALLEXCEPT ( COMPANY, COMPANY[Meter] ),
            YEAR ( COMPANY[Date] ) <= Current_Year - 2
                && YEAR ( COMPANY[Date] ) >= Current_Year - 3
        )
    )
RETURN
    ( Present_Average_Area_size_per_meter - Past_Period_Average_Area ) / Present_Average_Area_size_per_meter

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yuta-msft 

Thanks a million.