Get value of second table based on multiple conditions

[patrickbender]

Hi,

I have two tabels.

One with historic values of an asset and one with asset purchases.

I want to get the historic values of the asset purchases based on a what if parameter preferably using a measure.

For example if the what if parameter is 5 i would like to get the historic value 5 days earlier. Sometimes there won't be a historic value that specific date and then i would like to get the value of the first date before that date (for example 6 days earlier). 

Below are the structure of the tables.

Historic values table:

Asset ID

Date

Price

Asset purchases table:

Asset ID

Transaction date

Price

[patrickbender]

Thanks 🙂

[johnt75 · Accepted solution]

Try

Historic price =
VAR WhatIf = [What if value]
RETURN
    SUMX (
        'Asset purchases',
        SELECTCOLUMNS (
            TOPN (
                1,
                FILTER (
                    'Historic values',
                    'Historic values'[Asset ID] = 'Asset purchases'[Asset ID]
                        && 'Historic values'[Date] <= 'Asset purchases'[Date] - WhatIf
                ),
                'Historic values'[Date]
            ),
            "@value", 'Historic values'[Price]
        )
    )