Applying a value based on a date falling in a date range from a different table

[dojobrady]

I have a dataset that contains unique units (as in products) that were sold. In the dataset, there is a column for a "scan date", as in, when it was scanned to originally be delivered to us before we sold it. I have a unique key in this table that groups the "category" and "facility source" as one key, so something like "electronicsnewyork" as an example.

In another table, I have a key that can match the original key exactly, because it is a list of categories and facility sources, plus a column that is for the "iteration" of the product, with a min and a max date field that tell me the dates of which the iteration is true. See an example below.

key                           iteration    min date      max date

electronicsnewyork      1          1/1/2019       3/30/2019

electronicsnewyork      2          3/31/2019       8/30/2019

electronicsnewyork      3          9/1/2019       

What I'm trying to do is look up key against key, and then look at the "scan date" and see which min-max date range it falls under in the other table, and then tell me what iteration the product is based on that. 

I have 144 combinations of keys in total, so an efficient solution would be amazing!

[v-xuding-msft · Accepted solution]

Hi @dojobrady ,

I created a measure that may help you.

Measure = 
CALCULATE (
    MAX(  Table1[Iteration] ),
    FILTER (
        Table1,
        AND (
            MAX ( Table2[Scan date] ) >= Table1[Min date],
            MAX ( Table2[Scan date] ) <= Table1[Max date]
        )
            || AND (
                MAX ( Table2[Scan date] ) >= Table1[Min date],
                Table1[Max date] = BLANK ()
            )
    )
)

I attached my sample that you can download. If this is not what you want, please share more details and post the expected result. Then we will understand clearly about your request.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.