Find postcalcode in between range and lookup value based on second value

[Anonymous]

Hi,

Currently working on a case where I need to calculate estimated delivery and pickup times. Need to extract the leadtimes per postalcode/Transport Type to calculate this. I can't work with a key as I need to lookup a value in a range of table instead of an identical unique value in another table.

This is my table with orderdata:

I want to add a column with latest pick-up time. Therefore I need to include a column which displays the leadtime per order on every row.

This is my table with leadtime data:

So for example, lets take the first order from the image. This has postal code = AT1230 and D.S.T = U14. So lookup leadtimes where (Table1.[D.S.T]) = (Table2.[TT]) and find postal code AT1230 in between range from (Table2.[FromZip]) and (Table2.[ToZip]).

I think this is a very complex one to calculate but maybe there is an expert out there who knows the solution.

Thanks in advance.

Kind Regards,

Sofiën

[Anonymous · Accepted solution]

Found the solution for it with following column:

LeadtimeCalculation = LOOKUPVALUE('LeadTime'[Leadtime],Leadtime[TT code],G002[D.S.T]

,CALCULATE ( SELECTEDVALUE ( LeadTime[Leadtime] ),

FILTER (

LeadTime,

LeadTime[From] <= 'G002'[Zip Code]

&& LeadTime[To] >= 'G002'[Zip Code]

),FILTER(Leadtime, Leadtime[TT code] = 'G002'[D.S.T])

))