Find a record in a table with biggest date lower than the date of another table

[ijguelbenzu]

I have a table of invoices with a product and a date; an another table with product, date and discount and i need to find with discount i have to take. It must be the data of the record with biggets date lower than the date of the invoice.

[Anonymous]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI01Tew0DcyMDJQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, #"Invoice Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Invoice Date", type date}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "maxDisc", (invRow)=> Table.Max( Table.SelectRows(Disc, (discRow)=>discRow[Discount Date]<invRow[Invoice Date]), "Discount Date")),

    
    #"Expanded maxDisc" = Table.ExpandRecordColumn(#"Added Custom", "maxDisc", {"Product", "Discount Date", "% Discount"}, {"maxDisc.Product", "maxDisc.Discount Date", "maxDisc.% Discount"})
in
    #"Expanded maxDisc"

[Fowmy]

@ijguelbenzu 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[ijguelbenzu]

Table 1: Product = 'A'; Invoice Date = 15/08/2020

Table 2: Product = 'A'; Discount Date = 01/01/2020; % Discount = 1%

             Product = 'A'; Discount Date = 01/02/2020; % Discount = 2%

             Product = 'A'; Discount Date = 01/03/2020; % Discount = 3%

             Product = 'A'; Discount Date = 01/04/2020; % Discount = 4%

             Product = 'A'; Discount Date = 01/05/2020; % Discount = 5%

             Product = 'A'; Discount Date = 01/06/2020; % Discount = 6%

             Product = 'A'; Discount Date = 01/07/2020; % Discount = 7%

             Product = 'A'; Discount Date = 01/08/2020; % Discount = 8%

             Product = 'A'; Discount Date = 01/09/2020; % Discount = 9%

             Product = 'A'; Discount Date = 01/10/2020; % Discount = 10%

The discount I have to found is 8% because August the 1st is the biggest date lower than the August the 15th.

[Mariusz]

Hi @ijguelbenzu 

Try this in Power Query 

Table 2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc87CsAwDIPhqwRDt0Bt5z32HCH3v0a9qBSBlp9v0t6SHsmS1O6Yq2uUXXLyXxziLAVSWCqksjRIY+mQzjIgg2VCJsuCLBLT76kGnRc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Discount Date" = _t, #"% Discount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"% Discount", type number}, {"Discount Date", type date}})
in
    #"Changed Type"

Table 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc87CsAwDIPhqwRDt0Bt5z32HCH3v0a9qBSBlp9v0t6SHsmS1O6Yq2uUXXLyXxziLAVSWCqksjRIY+mQzjIgg2VCJsuCLBLT76kGnRc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Discount Date" = _t, #"% Discount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"% Discount", type number}, {"Discount Date", type date}})
in
    #"Changed Type"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Fowmy · Accepted solution]

@ijguelbenzu 

Add the following formula to your Table 1

Disc = 
VAR P = [Product]
VAR D = [Date]
RETURN
MAXX(
    FILTER(
        DiscountTable,
        DiscountTable[Product] = P && DiscountTable[Date] < D
    ),
    DiscountTable[Discount]
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[ijguelbenzu]

This is no exactly correct beacuse it found the biggest discount with a lower date; the correct solution is:

Descuento =

VAR P = [ItemCode]

VAR D = [DocDate]

var F = MAXX(

FILTER(

'Condiciones',

'Condiciones'[ItemCode] = P && 'Condiciones'[FechaInicio] < D

),

'Condiciones'[FechaInicio]

)

RETURN

MAXX(

FILTER(

'Condiciones',

'Condiciones'[IItemCode] = P && 'Condiciones'[FechaInicio] = F

),

'Condiciones'[Dto]

)