Sum By User ID Between Two Dynamic Dates

[jackj]

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.

UserID	Date	Sales	Last 7 Days Sales	Last 14 Days Sales
123	12/31/2022	$10.00	???	???
123	12/10/2022	$5.00	???	???
124	12/24/2022	$15.00	???	???
124	12/10/2022	$20.00	???	???
125	12/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!!

[SamInogic]

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 crm@inogic.com

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

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

[ImkeF · Accepted solution]

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.