Forum Discussion

patrickbender's avatar
patrickbender
Icon for Helper I rankHelper I
3 years ago
Solved

Get value of second table based on multiple conditions

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

 

"CALCULATE"
"MEASURE"
DAX
what if parameters
  • johnt75's avatar
    johnt75
    3 years ago

    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]
        )
    )

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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]
        )
    )