AVG Sales Price Index Calculation

[Anonymous]

i have the following date realestate data:

Avarage Sales Price per SQM which is a measure.

the following is the formula i used to create the measure from avalable data:

AVG SALES PRICE PER SQM = DIVIDE([SUM OF RS PRICE], [SUM OF AREA], 0)

now i want to calculate the index. and that is:

(Present Average sales price per square meter) - (Past Period Average sales price per square meter) / (Past Period Average sales price per square meter)

by Period i mean either year or quarter. 

how can i creat the above formula.?

thanking you

[v-xicai]

Hi  @Anonymous ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.

Best regards

Amy Cai

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the Date column of your Real Estate Table to the Date column of your Calendar Table.  In the Calendar Table, extract the Year by using this calculated column formula - Year = YEAR(Calendar[Month]).  To your slicer, drag Year from the Calendar Table and select any one year.  Write this measure

AVG SALES PRICE PER SQM IN PREVIOUS YEAR = CALCULATE([AVG SALES PRICE PER SQM],PREVIOUSYEAR(Calendar[Date]))

Hope this helps.

[v-xicai]

Hi @Anonymous ,

You can create measure like DAX below, assuming by the period year, as well you can use YAER(Table1[Date]) to get Year column or use ROUNDUP(MONTH(Table1[Date])/3,0) to get Quarter column first of all.

Measur1 =

VAR _previous = CALCULATE([AVG SALES PRICE PER SQM ],FILTER(ALLSELECTED(Table1), Table1[Year] =MAX(Table1[Year]) -1))

VAR _current = CALCULATE([AVG SALES PRICE PER SQM ],FILTER(ALLSELECTED(Table1), Table1[Year] =MAX(Table1[Year])))

return

IF(_previous<>BLANK(),DIVIDE(_current-_previous, _previous),BLANK())

Best Regards,

Amy

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

[Anonymous]

Thank you for your reply, 

I tried to creat the quarter with the given ROUNDUP formula

but the result as you see, first its not accepting the zero 

and if i remove the zero its saying too few argument.

while u check this i will creat a quarter as follows 

QUARTER = FORMAT(table1[datecolumn].[Date],"Q")

will this be ok?

[v-xicai · Accepted solution]

Hi @Anonymous ,

Please try this one ROUNDUP(MONTH(Table1[Date])/3,0) .

Best Regards,

Amy

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

[Anonymous]

Again its not accepting 

ROUNDUP(MONTH(Table1[Date])/3,0) 

when i come to type the [DATE] after the table name its only showing option to measures, not giving option select the data column

[Anonymous]

There is also error in the second formula, not accepting VAR _current

[v-xicai]

Hi @Anonymous ,

Based on my test , it worked fine. Please check the brackets (plus or miss) in the two DAX formula, and try it again.

Best Regards,

Amy

[Anonymous]

Hello Amy, 

I got the quarter column. now what about the measure that u suggested. i have the following error when i try to create.

[v-xicai]

Hi @Anonymous,

Please pay attention to these symbols in the DAX formula,delete it, and try it again.

Best Regards,

Amy