Custom Funtion to add the total sales comes from another table taking into account two variables

[Syndicate_Admin]

Hello

I hope you do well. I need your help. Now I'm starting with learning functions in M and I've been stuck with an assumption.

Table Nº 1. Sales table:

Let

#"sales table"=
#table (
type table [Product= text, Date=date, Amount= number],
{
{"Product A",#date(2023,03,01),1000},
{"Product A",#date(2023,03,02),500},
{"Product A",#date(2023,03,03),750},
{"Product B",#date(2023,03,01),2000},
{"Product B",#date(2023,03,02),1500},
{"Product B",#date(2023,03,03),1750},
{"Product C",#date(2023,03,01),500},
{"Product C",#date(2023,03,02),250},
{"Product C",#date(2023,03,03),375}

}
)
in
#"sales table"

Table No. 2. Range table

Let

#"range table"=
#table(

{"Product","DateRange"},

{
{"Product A",{"01/03/2023","03/03/2023"}},
{"Product B",{"02/03/2023","03/03/2023"}},
{"Product C",{"01/03/2023","02/03/2023"}}
}

)
in
#"range table"

I need a function to be able to achieve this result in table Nº2

Please I need help.

Thank you

[Syndicate_Admin]

Hello

Excellent solution. Thank you very much for your help. We continue to learn from this wonderful M language.

We keep in touch.

[ThxAlot]

let
    #"sales table"=
        #table (
            type table [Product= text, Date=date, Amount= number],
            {
                {"Product A",#date(2023,03,01),1000},
                {"Product A",#date(2023,03,02),500},
                {"Product A",#date(2023,03,03),750},
                {"Product B",#date(2023,03,01),2000},
                {"Product B",#date(2023,03,02),1500},
                {"Product B",#date(2023,03,03),1750},
                {"Product C",#date(2023,03,01),500},
                {"Product C",#date(2023,03,02),250},
                {"Product C",#date(2023,03,03),375}
            }
        ),
    #"range table"=
    #table(
        {"Product","DateRange"},
        {
            {"Product A",{"01/03/2023","03/03/2023"}},
            {"Product B",{"02/03/2023","03/03/2023"}},
            {"Product C",{"01/03/2023","02/03/2023"}}
        }
    ),
    Joined = Table.NestedJoin(#"range table", "Product", #"sales table", "Product", "Joined", JoinKind.LeftOuter),
    #"Filtered Amount" = Table.ReplaceValue(Joined, each List.Transform([DateRange], Date.From), null, (x,y,z) => List.Sum(Table.SelectRows(x, each y{0}<=[Date] and [Date]<=y{1})[Amount]), {"Joined"})
in
    #"Filtered Amount"

[amitchandak]

@Syndicate_Admin , I adjusted the date to suit the US format in Table 2. I added this new column

let 
_st = Date.From([DateRange]{0}),
_end = Date.From([DateRange]{1}),
_prd = [Product],
_data =  List.Sum(Table.SelectRows(Table1,each [Date] >= _st and [Date]<=_end and [Product] =_prd )[Amount])
in 
_data

You can also find the file attached after signature