Search code examples
daxpowerquerymeasure

use of a related table value in a measure

I have a list of stock in a table with some other parameters including a date when I bought the stock. I have another table with daily values. Both are related using the stock code. This daily values table includes as well as values from an index that is not a stock.

I'm trying to sum the index performance (i have a column for the daily performance) since the acquisition of the stock (the date of acquisition is in the list table).

Last, I'm new to DAX. I have a first measure to identify the acquisition date :

acquisition_date :=
LOOKUPVALUE (
    stock_list[aqui_date];
    stock_list[Code]; MAX ( daily_values[Code] )
)

that's the only way I found to get that value as a "related" function doesn't work.

Then another measure :

index_perfor :=
CALCULATE (
    SUM ( daily_values[perf] );
    FILTER ( ALL ( daily_valus ); daily_values[Date] >= [acquisition_date] )
)

When I use the measure in a pivot which I want stock by stock (in rows), I have the same result for all the rows (the result for each of the rows is the total result, which is not taking into account any acquisition date).

Anyone knows why? I can explain further if needed.

Solution

  • When you call a measure inside of an iterator (like FILTER), it's evaluated within the row context (so is potentially different for each row). I think you want it to be a constant, so let's calculate it once and save that as a variable.

    index_perfor :=
VAR AcqDate = [acquisition_date]
RETURN
    CALCULATE (
        SUM ( daily_values[perf] );
        FILTER ( ALL ( daily_valus ); daily_values[Date] >= AcqDate )
    )