cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Thank you

3 REPLIES 3

Hello

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

We keep in touch.

Super User
``````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"``````

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.