cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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!

 

Mr_Glister_0-1600153668654.png

 

4 REPLIES 4
Super User IV
Super User IV

@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]))

Fowmy_0-1600161256046.png

________________________

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 🙂

YouTube  LinkedIn

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi there,

I would use a comparable approach like @Fowmy , just for performance reasons I would skip the 

each [Customer ID] = cust

 part and would apply the rest as a function on the grouped partitions instead, like described here:

https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power...

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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!

Super User II
Super User II

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


Connect on LinkedIn

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors