Lookup values (or other method?) from same table

[estewart]

I am trying to create a new column that assigns trend "start date" to trend "data value". See table below. Columns in gray are what I have currently. Col A has two variable types "Trend_x" which is the data value and Trend_x_Start" which is the start date. YearQuarter represents different quarterly forecasts. Value contains either the data value or the trend start date (e.g. YYYY.Q format).

Trend start date is not consistent across all (a) Trend_x and (b) YearQuarter combinations.

I am trying to create the column in green at the query or table level using DAX. So far I haven't been able to get it to work.

Any help is appreciated. Thank you.

[v-piga-msft · Accepted solution]

Hi @estewart ,

Sorry for the delay.

For your sceanrio, you could try the steps below.

1. Go to Query editor and duplicate the original table and filter rows with Variable.

2. Apply and Close , create a calcualted column in table 2 with the formula below.

Column = IF('Table2'[Variable] = "Trend_1_Start",1,2)

3.  Create two calculated columns in Table.

Column = IF('Table1'[Variable] in {"Trend_1","Trend_1_Start"},1,2)

Column 2 =
LOOKUPVALUE (
    'Table2'[Value],
    'Table2'[YearQuarter], 'Table1'[YearQuarter],
    Table2[Column], 'Table1'[Column]
)

Here is the output.

More details please refer to my attachment.

Best  Regards,

Cherry