Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mr_Glister
Advocate II
Advocate 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
Fowmy
Super User
Super User

@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! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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!

tex628
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.