Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

AVG Sales Price Index Calculation

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

 

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    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.

     

10 Replies

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

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-xicai
        Icon for Community Support rankCommunity Support
        7 years ago

        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.

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    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