Power BI : Add column that Calculates Estimated Market Price

[azale2407]

So, I am trying to add a column in Power BI to estimate what an average price would be based on historical data.  Basically, each row in my table has data for a specific loan.  When a loan is first marketed for sale we do not know what the buyers will ultimately price it at.  So I want to have a calcuation that filters by similar loans and a date range to get an estimated price for each loan even before it goes out to market for actual pricing.

What I need:

1. Column that calculates estimated/average historical price,

2. Filtered by: Date previous 180 days / Interest rate plus-minus .25 / Exactly LTV Range / Exact Loan Type

Below is the formula I have attempted.  I was trying to add within Power BI, but maybe I need to do something like this in Power Query.  Just not sure.  Any help would be appreciated.  Note: LTV Range and Loan Type simply filter by exact text matches.  i.e. each loan will have a column for LTV of either 0-80, 80-90, or 90-100.  And each loan will have a column with of Conv, Jumbo, etc for loan type. 

EstimatedMarketPrice = DIVIDE (

    CALCULATE ( SUM ( 'Bid Sheet'[Win Bid] ),

        'Bid Sheet'[Date] <= ('Bid Sheet'[Date] - 1),

        'Bid Sheet'[Date] >= ('Bid Sheet'[Date] - 181),

        'Bid Sheet'[InterestRateFromTapeData] >= ('Bid Sheet'[InterestRateFromTapeData] - .25),

        'Bid Sheet'[InterestRateFromTapeData] <= ('Bid Sheet'[InterestRateFromTapeData] + .25),

        'Bid Sheet'[LTVFromTapeData] = 'Bid Sheet'[LTVRange],

        'Bid Sheet'[LoanTypeFromTapeData] = 'Bid Sheet'[LoanType]),

    CALCULATE (COUNTA ( 'Bid Sheet'[Win Bid]),

        'Bid Sheet'[Date] <= ('Bid Sheet'[Date] - 1),

        'Bid Sheet'[Date] >= ('Bid Sheet'[Date] - 181),

        'Bid Sheet'[InterestRateFromTapeData] >= ('Bid Sheet'[InterestRateFromTapeData] - .25),

        'Bid Sheet'[InterestRateFromTapeData] <= ('Bid Sheet'[InterestRateFromTapeData] + .25),

        'Bid Sheet'[LTVFromTapeData] = 'Bid Sheet'[LTVRange],

        'Bid Sheet'[LoanTypeFromTapeData] = 'Bid Sheet'[LoanType])

)

[v-cgao-msft · Accepted solution]

Hi @azale2407 ,

Please try this:

EstimatedMarketPrice =
DIVIDE (
    CALCULATE (
        SUM ( 'Bid Sheet'[Win Bid] ),
        FILTER (
            ALL ( 'Bid Sheet' ),
            'Bid Sheet'[Date]
                <= EARLIER ( 'Bid Sheet'[Date] ) - 1
                && 'Bid Sheet'[Date]
                    >= EARLIER ( 'Bid Sheet'[Date] ) - 181
                && 'Bid Sheet'[InterestRateFromTapeData]
                    >= EARLIER ( 'Bid Sheet'[InterestRateFromTapeData] ) - .25
                && 'Bid Sheet'[InterestRateFromTapeData]
                    <= EARLIER ( 'Bid Sheet'[InterestRateFromTapeData] ) + .25
                && 'Bid Sheet'[LTVFromTapeData] = 'Bid Sheet'[LTVRange]
                && 'Bid Sheet'[LoanTypeFromTapeData] = 'Bid Sheet'[LoanType]
        )
    ),
    CALCULATE (
        COUNTA ( 'Bid Sheet'[Win Bid] ),
        FILTER (
            ALL ( 'Bid Sheet' ),
            'Bid Sheet'[Date]
                <= EARLIER ( 'Bid Sheet'[Date] ) - 1
                && 'Bid Sheet'[Date]
                    >= EARLIER ( 'Bid Sheet'[Date] ) - 181
                && 'Bid Sheet'[InterestRateFromTapeData]
                    >= EARLIER ( 'Bid Sheet'[InterestRateFromTapeData] ) - .25
                && 'Bid Sheet'[InterestRateFromTapeData]
                    <= EARLIER ( 'Bid Sheet'[InterestRateFromTapeData] ) + .25
                && 'Bid Sheet'[LTVFromTapeData] = 'Bid Sheet'[LTVRange]
                && 'Bid Sheet'[LoanTypeFromTapeData] = 'Bid Sheet'[LoanType]
        )
    )
)

If this doesn't work, please consider providing some sample data and expected output.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523