Forum Discussion
Sum By User ID Between Two Dynamic Dates
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.
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/