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

Power Query custom column sum last 5 days

Hi Guys, 

 

I have the following table columns: 

 

Posting date - Number of orders - Total sales

 

Now I want an extra custom column that sums the sales of every posting days - 5 days back. 
So on 13-11-2019 i want that the custom column shows me the sum of total sales from the dates 13-11 / 12-11 / 10-11 / 09/11 and 08/11. 

I know there are easy measures to fix this, but i really need it as a custom column in my table. 

Can somebody help me? 

 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Power Query custom column sum last 5 days

Hi @robbinkees ,

You can refer to the following sample if it suitable for your requirement:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZLLjQNRCARz8dnCNH9iWTn/NPZZGpg5lxo1BX9/L3zwEUa/3i9hqnDOZnt93z8kg1qJnaU09EI6yIuiubxkUjYom06iApIX8kFRBATCbFKxKaeoMveMC+UgNRIF9DS5UA0qJk1ms5pUb0oJrirJuBB4F2sKjWibgVgd7T/kjuhh8hjJ3vDYjlghUqfJ6djYmWskjmK2VORWWSV2aiZba45j3E6YpDJKW4blw7KhHqfBSgkj1XYu7AprpYy4VVrV59prRYssvLN7Zgoel4sTYveZKavFmtxDxHzWk/tPhM5I7vT9LnvMzJb21s2tlgJBDBrYnqvFk6pNJHV7rhZJMj2PeZLDbi8g53DT+9PXSyi1QUR1zqf8cKZHKG6ful4yKNIB/Hb//gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Rolling 5 Days", each Function.Invoke((current as date,tb as table)=> List.Sum(Table.SelectRows(tb,each [Date]>=Date.AddDays(current,-5) and [Date]<=current)[Amount]),{[Date],#"Changed Type"}))
in
    #"Added Custom"

18.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: Power Query custom column sum last 5 days

Hi @robbinkees ,

Can you please share some sample data for test? I 'm not clear your data structure and it is hard to coding formula without any detail information.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Power Query custom column sum last 5 days

Hi @v-shex-msft 

 

 

This is an example of the data. 
I need an extra column that sums the total sales of 5 days back from that date. 

So on 6-1-2019 it's a sum of 6-1 5-1 4-1 3-1 and 2-1


Knipsel.PNG

Highlighted
Community Support
Community Support

Re: Power Query custom column sum last 5 days

Hi @robbinkees ,

You can refer to the following sample if it suitable for your requirement:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZLLjQNRCARz8dnCNH9iWTn/NPZZGpg5lxo1BX9/L3zwEUa/3i9hqnDOZnt93z8kg1qJnaU09EI6yIuiubxkUjYom06iApIX8kFRBATCbFKxKaeoMveMC+UgNRIF9DS5UA0qJk1ms5pUb0oJrirJuBB4F2sKjWibgVgd7T/kjuhh8hjJ3vDYjlghUqfJ6djYmWskjmK2VORWWSV2aiZba45j3E6YpDJKW4blw7KhHqfBSgkj1XYu7AprpYy4VVrV59prRYssvLN7Zgoel4sTYveZKavFmtxDxHzWk/tPhM5I7vT9LnvMzJb21s2tlgJBDBrYnqvFk6pNJHV7rhZJMj2PeZLDbi8g53DT+9PXSyi1QUR1zqf8cKZHKG6ful4yKNIB/Hb//gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Rolling 5 Days", each Function.Invoke((current as date,tb as table)=> List.Sum(Table.SelectRows(tb,each [Date]>=Date.AddDays(current,-5) and [Date]<=current)[Amount]),{[Date],#"Changed Type"}))
in
    #"Added Custom"

18.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Power Query custom column sum last 5 days

@v-shex-msft 

 

Do i just need to ad an custom column with this code? 

Table.AddColumn(#"Changed Type", "Rolling 5 Days", each Function.Invoke((current as date,tb as table)=> List.Sum(Table.SelectRows(tb,each [Date]>=Date.AddDays(current,-5) and [Date]<=current)[Amount]),{[Date],#"Changed Type"}))

 

Or what do i have to do, i'm quite new in the whole powerquery part.  

When  i try this, i get not the same result as you @v-shex-msft 

Highlighted
Community Support
Community Support

Re: Power Query custom column sum last 5 days

HI @robbinkees ,

You can add a custom column and paste above formula in it:

Function.Invoke((current as date,tb as table)=> List.Sum(Table.SelectRows(tb,each [Date]>=Date.AddDays(current,-5) and [Date]<=current)[Amount]),{[Date],#"Changed Type"})

Notice: replace bold part with your column and steps name.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Power Query custom column sum last 5 days

Great code! Thank you 

 Any advice to adding groups to the code? I.e. accumulate last five days' sales for each product? 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors