Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Syndicate_Admin
Administrator
Administrator

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"

Leo_Tribaldo_1-1682960959694.png

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"

Leo_Tribaldo_2-1682960986931.png

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

Leo_Tribaldo_0-1682960888566.png


Please I need help.

Thank you

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

Hello

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

We keep in touch.

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

ThxAlot_0-1682968265514.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



amitchandak
Super User
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

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.