Measure to calculate Median Sale Price


#1

Hi, I need to create Measure that calculates median price between [Sale price1] and [Sale price2] based on Sale date1 and Sale date2. Following is the output I am looking for. I have created new Inactive relationship between Sale date2 and Dim date and active relation between Sale date1 and Dim date.
I have created following DAX but its not working as expected.
Dax: Price2 % difference from Price1 =
VAR __BASELINE_VALUE = SUM(‘Fact Sale’[Price1])
VAR __VALUE_TO_COMPARE = CALCULATE(SUM(‘Fact Sale’[Price2]),
USERELATIONSHIP(‘Fact Sale’[Saledate2],DimDate[Date])
,KEEPFILTERS(DimDate[Date] >= DATE(2010,01,01)
&& DimDate[Date] < DATE(2015,01,01)
) )
RETURN
IF(
NOT ISBLANK(__VALUE_TO_COMPARE),
DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
)

Expected output:


#2

Can you post some sample data?


#3
SALESEVIDENCE_SID PROPERTY_SID PROPERTYTYPE_SID SUBURB_SID SALETYPE_SID SALECONTRACTDATE_SID SalePrice SALECONTRACTDATE2_SID SalePrice2
390301 1775684 169 113 4 20181231 533000 20170405 260000
796885 12591016 439 3708 4 20181231 264000 -1 0
1022212 -1 169 3252 4 20181231 650000 20010401 250000
1161289 -1 138 2415 4 20181231 520000 20150124 650000
463299 1761078 169 2494 4 20181231 305000 20130429 419000
540861 -1 439 669 4 20181231 192800 -1 0
611667 12565606 327 350 4 20181231 495000 19900530 150000
1821463 -1 169 1122 4 20181231 625000 20040627 353000
1822937 -1 169 2489 4 20181231 299000 20110405 300000
2269925 -1 169 2533 4 20181231 420000 -1 0

This is Fact table which will join to Dim_Date on SaleContractdate_SID(Active) and SaleContractdate2_SID(Inactive)


#4

@guptaopus
Can you take a look at the attached file and see if we are heading in the right direction? Using the data from above I was able to produce the following:

Have a the segments of when it was purchased on rows and the year that it was sold on columns. Then a few measures. It’s all in the file

Median Sales Price.pbix (195.2 KB)