Measure To Calculate Median Sale Price

DAX DAX Calculations
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:

image
image.png720×373 12.7 KB

2

Can you post some sample data?

Enterprise%20DNA%20Expert%20-%20Small

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:

Matrix%20v1
Matrix%20v1693×275 20.9 KB

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)

Enterprise%20DNA%20Expert%20-%20Small

1 Like
5

Hi,
Sorry for the late reply, I Was on leave. This is the solution I was look for. Thanks a lot