Look for value with multiple conditions

[Anonymous]

Need help on this Power BI DAX, please see the test Power BI File attached.

In the PriceHistory table contains MemberId, Price and the [Date] when the price was captured

In the Member table contain memberId and reporDate.

I need to calculate a column [Price] in the member table to look for the rows for the memberId in PriceHistory table where [Date] is less than the [reportDate] and take the price from the row with max[Date] from the rows.

Data in PriceHistory:

Data in Member:

Expecting result:

The tricky parts the price captured for Member Id 3 on 1st Jun 2022 will be used for both July and Aug report as there is no new price captured for Member 3

after 1st Jun 2022

https://www.dropbox.com/s/kbpt7cqilgd30ed/test.pbix?dl=0

[DataInsights · Accepted solution]

@Anonymous,

Try this calculated column in table Member:

Price = 
VAR vMemberId = 'Member'[MemberId]
VAR vReportDate = 'Member'[reportDate]
VAR vTable =
    FILTER (
        PriceHistory,
        PriceHistory[MemberId] = vMemberId
            && PriceHistory[Date] < vReportDate
    )
VAR vMaxDate =
    MAXX ( vTable, PriceHistory[Date] )
VAR vResult =
    MAXX ( FILTER ( vTable, PriceHistory[Date] = vMaxDate ), PriceHistory[Price] )
RETURN
    vResult