Lookup column with dates in PowerPivot

Question

I need to add a column on a table, computed from another one but I can't get the formula right. Here are the tables and columns:

Table 1
Date (date)

Table 2
Key (text)
Start (date)
End (date)

I want to add a column to the Table 1 which will contains the Key from Table 2 where the Date column is between Start and End from Table 2.

If the date cannot be found in a range, then the field should be blank.

Somthing like that: =CALCULATE(Table2[Key]; FILTER(Table2; [Date]>=Table2[Start]&&[Date]<=Table2[End])) — Nicolas C, Sep 21 '16 at 09:06
I've tried other formulas more or less like this one, but none worked... — Nicolas C, Sep 21 '16 at 09:17

score 1 · Accepted Answer · answered Sep 21 '16 at 20:57

1

Try creating a calculated column in Table1 called Key and use this expression:

=
CALCULATE (
    LASTNONBLANK ( Table2[Key], 0 ),
    FILTER ( Table2, Table1[Date] >= Table2[Start] && Table1[Date] <= Table2[End] )
)

I am assuming in your table2 there is no overlaps between dates and every date in Table1 will match only one start-end range.

It is not tested but should work, let me know if this works for you.

answered Sep 21 '16 at 20:57

alejandro zuleta

1 Answers1