Forum Discussion
Vertical sum between two dates in Power Query
Hi,
I have a table with "Date" (=Transaction date), "Start of period" date (chosen here to be 10 days before the Transaction date), Customer ID, and Value. What I would like to do is add a custom column that calculates the sum of "Value" for the specific Customer ID between my transaction date and the "Start of period" date.
Example below:
For the highlighted date (05/08/2020) and highlighted customer (17357) I want Power Query to go back up my Transaction dates until it finds my "Start of period" date, and then sum up the "Value" of all instances of 17357. Like I've highlighted, the customer comes up only once again on 29/07/2020 with Value 0. So the result in my highlighted row (05/08/2020 for customer 17357) should be 0+0 = 0
I think that List.Sum might be part of the solution but I don't know how to add a "Dates.Between" filter to it....
Hoping for somebody much smarter than me who can help me here! Thanks!
4 Replies
Mr_Glister
Add the following Custom Column:= Table.AddColumn(#"Changed Type1", "Custom", each List.Sum( Table.SelectRows(#"Changed Type1", let cust = [Customer ID] , sdate = [Start of period] , edate = [Date] in each [Customer ID] = cust and [Date] >= sdate and [Date] <= edate )[Value]))________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Hi there,
I would use a comparable approach like Fowmy , just for performance reasons I would skip the
each [Customer ID] = custpart and would apply the rest as a function on the grouped partitions instead, like described here:
Hi ImkeF und vielen Dank!
I followed your link and it looks very fancy but unfortunately I still can't quite figure out how to do the things you're suggesting. But I'd really like to know! Would you be able to be a bit more specific about how I would apply your solution? It would be highly appreciated!
Hi Mr_Glister,
The only power query solution i can think of here is expanding the date interval, merging the table with itself and then group it on the original rows. Do you really need to have this column in Power Query?
ImkeF , do you by any chance have a less performance heavy solution for this?
/ J
