Forum Discussion

jackj's avatar
jackj
Helper I
3 years ago
Solved

Sum By User ID Between Two Dynamic Dates

Hi, I have a very simple data table and I am looking to create a custom column in power query, to sum the subtotal of sales for a particular user ID in each row between dynamic dates as per each row.  In each row, I want to show the total sales for each ID for the last 7,14,21 days from the Date in each row.  For example, in row 1 I want to see the total sales for User ID 123 for the 7 day period between 12/25/22 and 12/31/22 and for the 14 day period between 12/18/22 and 12/31/22.  In row 2 I want to see the total sales for user ID 123 for the 7 day period between 12/4/22 and 12/10/22 and for the 14 day period between 11/27/22 and 12/10/22.

 

UserIDDateSalesLast 7 Days SalesLast 14 Days Sales
12312/31/2022$10.00??????
12312/10/2022$5.00??????
12412/24/2022$15.00??????
12412/10/2022$20.00??????
12512/24/2022$5.00??????

  

I do not want to do this in DAX.  How can I do this in power query?  Any help would be appreciated!!

 

Thank you!!

Need Help
Show and Tell
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi jackj ,
    for performance reasons, I would to this on grouped data (user level). So the code gets a lit complicated, unfortunately:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtIBkvrGhvpGBkZGQI6KoYGegYGCUqwOkrShAVzaFEnWBCJrZILQjEUaSbMRstmmGLqhmmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, Date = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"UserID", Int64.Type}, {"Date", type date}, {"Sales", Currency.Type}}), 
    Custom1 = Table.Group(
        #"Changed Type", 
        {"UserID"}, 
        {
            {
                "UserID_Partition", 
                (P) =>
                    Table.AddColumn(
                        Table.Buffer(P), 
                        "SalesM", 
                        each [Sales14 = List.Sum(Table.SelectRows(
                            P, 
                            (Partition) => Partition[Date] >= Date.AddDays(_[Date], - 14) and Partition[Date] <= _[Date]
                        )[Sales]),
                        Sales7 = List.Sum(Table.SelectRows(
                            P, 
                            (Partition) => Partition[Date] >= Date.AddDays(_[Date], - 7) and Partition[Date] <= _[Date]
                        )[Sales])
                        ]
                    )
            }
        }
    ),
    #"Expanded UserID_Partition" = Table.ExpandTableColumn(Custom1, "UserID_Partition", {"Date", "Sales", "SalesM"}, {"Date", "Sales", "SalesM"}),
    #"Expanded SalesM" = Table.ExpandRecordColumn(#"Expanded UserID_Partition", "SalesM", {"Sales14", "Sales7"}, {"Sales14", "Sales7"})
in
    #"Expanded SalesM"

    Pls also check the file enclosed.

     

2 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi jackj ,
    for performance reasons, I would to this on grouped data (user level). So the code gets a lit complicated, unfortunately:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtIBkvrGhvpGBkZGQI6KoYGegYGCUqwOkrShAVzaFEnWBCJrZILQjEUaSbMRstmmGLqhmmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, Date = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"UserID", Int64.Type}, {"Date", type date}, {"Sales", Currency.Type}}), 
    Custom1 = Table.Group(
        #"Changed Type", 
        {"UserID"}, 
        {
            {
                "UserID_Partition", 
                (P) =>
                    Table.AddColumn(
                        Table.Buffer(P), 
                        "SalesM", 
                        each [Sales14 = List.Sum(Table.SelectRows(
                            P, 
                            (Partition) => Partition[Date] >= Date.AddDays(_[Date], - 14) and Partition[Date] <= _[Date]
                        )[Sales]),
                        Sales7 = List.Sum(Table.SelectRows(
                            P, 
                            (Partition) => Partition[Date] >= Date.AddDays(_[Date], - 7) and Partition[Date] <= _[Date]
                        )[Sales])
                        ]
                    )
            }
        }
    ),
    #"Expanded UserID_Partition" = Table.ExpandTableColumn(Custom1, "UserID_Partition", {"Date", "Sales", "SalesM"}, {"Date", "Sales", "SalesM"}),
    #"Expanded SalesM" = Table.ExpandRecordColumn(#"Expanded UserID_Partition", "SalesM", {"Sales14", "Sales7"}, {"Sales14", "Sales7"})
in
    #"Expanded SalesM"

    Pls also check the file enclosed.

     

  • SamInogic's avatar
    SamInogic
    Super User
    3 years ago

    Hi,

    As per our understanding, the user wants the Sum of the [sales] Column by the Column [User] without DAX and Calculated Column instead you want to achieve it using Power Query.

    This is our Sample table.

     

    Duplicate this Table by clicking right on Sample Table and on to the Duplicate Table, we have to use Group by under the Home Tab, With Mentioned Columns:


    This Group by will Return Sum of sale by the User ID:

    After this, we have to Merge the Table with User id Column in Sample Table and then click on the merge Queries in Home tab.

    In the below Image, Sample table has two columns [Last 7 days sales] and [Last 14 days sales] which is not necessary , we have created the same with Expression. 

     

    Now we can get The Sum of Sales Column in Sample table,

    Now we have to create two custom columns for Date to check for last 7 days and last 14 days

    The column will have 0,1 as value 

    1: The row is in between 7 and 14 days,

    0: Not between the 7 and 14 Days,

    Let’s See for the last 7 days (14 days as follows)

     

    By using this expression, let’s create a custom Column named [7 days bool]

    Similarly, we can create the same for 14 Days by replacing 7 to 14 

    Last 7 Days bool = if(Duration.Days(DateTime.Date(DateTime.LocalNow())-[Date])<=7) then 1 else 0

    Last 14 Days bool = if(Duration.Days(DateTime.Date(DateTime.LocalNow())-[Date])<=14) then 1 else 0


    Now we can create our desired Columns to see the Sales as per day 

    Let’s get the Last 7 days Sale sum 

    Last 7 Days sales Sum = if([Last 7 Days Bool] = 1) then [#"Sample Table (2).Sale Sum "] else 0

    Last 14 Days sales Sum =  if([Last 14 Days Bool] = 1) then [#"Sample Table (2).Sale Sum "] else 0

    The Sales and sales Sum is Whole number we can change the Type, 

    for this Change type of column for Desired Columns

    This would look like below table,

     

    If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

    Thanks!

    Inogic Professional Services

    An expert technical extension for your techno-functional business needs

    Power Platform/Dynamics 365 CRM

    Drop an email at [email protected]

    Service:  http://www.inogic.com/services/ 

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/