Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Comparing Change in data in 2 time periods

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.

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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.

     

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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.