Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 ,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |