Lookup between 2 days

[LudivineLOU]

Hello, 

I have a price table with different article and price validate for the period between "valid from" until "valid to"

Table price :

Article      valid from       valid to              price

   A         01.01.2020         31.12.2021          10

   A         01.01.2022         15.06.2022           12

   A         16.06.2022         31.12.2030           14

   B         01.03.2020         31.12.2021          100

   B         01.01.2022         15.08.2022           120

   B         16.08.2022         31.12.2030           160

I have a table order with my order qty

order date      order        article    price

02.04.2020        raw1            A           ?*

02.04.2020        raw1            B            ?**

17.08.2022        raw1            B            ?***

I need to find the price of the article based on the order date. 

In the table bellow, the price must be 10 (?*), 100(?**), 160(?***)

Could you please help me with a formula dax ?

Thank you,

Best regards. 

[Greg_Deckler · Accepted solution]

@LudivineLOU (1) LOOKUPVALUE Range - Microsoft Fabric Community

[lbendlin · Accepted solution]

Does it need to be DAX?

Prices:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY5RCoAgEESvEn6L7Kwl/tY1xLt5Fk/WalQWmTAsDOxjXggqpymntd4rSiuCkTAxScnJwoBLhVSQinpMciWxGHJN5T8W7vF8rtpDAvPNbp+7tmtMI/Sl7FvlX7g4+76zEzju", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, #"valid from" = _t, #"valid to" = _t, price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Article", type text}, {"valid from", type date}, {"valid to", type date}, {"price", Currency.Type}},"de"),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Article", Text.Trim, type text}})
in
    #"Trimmed Text"

Orders:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSMzDRMzIwMlDSUTq0QKEosdzw0AIIG4gcgexYHYLKnKDKDM31DCxAyoxwK4sFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"order date" = _t, #"order " = _t, article = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"order date", type date}, {"order ", type text}, {"article", type text}},"de"),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"order ", Text.Trim, type text}, {"article", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(
  #"Trimmed Text", 
  "Price", 
  (k) =>
    Table.SelectRows(
      Prices, 
      each [Article] = k[article] and [valid from] <= k[order date] and k[order date] <= [valid to]
    ){0}[price], 
  Currency.Type
)
in
    #"Added Custom"

[Greg_Deckler · Accepted solution]

@LudivineLOU (1) LOOKUPVALUE Range - Microsoft Fabric Community