Calculate sum of sales of the last 365 days in a data flow

[webportal]

Hello,

My table looks like the following:

CustomerNo | Date             | Sales

111                | 20-12-2020  | 100

112               | 21-12-2020  | 500

112               | 22-12-2020  | 400

...

I want to calculate for each CustomerNo the sum of Sales of the last 365 days.

Note: I need this in a Data Flow, so I'm looking for Power Query. No DAX please.

Thank you so much in advance for your help!

[webportal]

@Jimmy801 

Thank you, it is almost perfect.

It is calculating the sales of the last 365 days referencing Now, and I need per each transaction.

I'll try to tweak the code you provided.

[Jimmy801]

Hello @webportal 

to be precise it calculate the sales of the last year 🙂

ok, then you have to include the ta-column in your grouping as well. Give it a try and let me know. If you need any help, feel free to post here.

BR

Jimmy

[webportal]

Table.AddColumn(#"Linhas Agrupadas" ,"RunningTotal",(i)=>List.Sum(Table.SelectRows(#"Linhas Agrupadas" , each [CustomerNo]=i[CustomerNo] and [Data]>Date.AddDays(i[Data],-365) and [Data]<=i[Data] ) [Sales]), type number )

This one solve it!

The only issue now is really the time it takes to compute.

[Jimmy801]

Hello @webportal 

but you need this information on every row of your dataset? This would create redundant data. I used the group function to sum up your dataset, not to keep all of it. That is the difference. In your code basically you have to filter all your data on every row, and that makes it slow. add some Table.Buffer in the Table.SelectRows to prevent rereading your dataset on every row

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[webportal]

@Jimmy801 

Thanks, that makes sense.

Table.buffer is a Power Query M function which is not well documented, unfortunately.

Do you know how to adapt it to this particular case?

[Jimmy801 · Accepted solution]

Hello @webportal 

so you really need this data on every row? To implement Table.Buffer you can try this code

TblBuffer = Table.Buffer(#"Linhas Agrupadas"),

Table.AddColumn(TblBuffer  ,"RunningTotal",(i)=>List.Sum(Table.SelectRows(TblBuffer  , each [CustomerNo]=i[CustomerNo] and [Data]>Date.AddDays(i[Data],-365) and [Data]<=i[Data] ) [Sales]), type number )

and your code by the way doesn't use the criteria of 365 days from now on, but uses the date of the current row to calculate -365.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[webportal]

I get an error on the 1st step:

Expression.Error: Não é possível converter o valor "xxx para o tipo Table.
Detalhes:
Value=BaseClienteMes1
Type=[Type]

It doesn't matter.

It's too complicated and it's not worth the effort, I'll just do it R.

Thank you so much for your time and sorry for all this mess.

[Jimmy801]

Hello @webportal 

but did you solve your problem?

BR

Jimmy

[webportal]

Actually, no.
My intention was to calculate the sales of the last 365 days in a data flow, but since it is so complicated, I'll do it in R, export the results to a txt file and then read that txt file and combine it with the data source.

Thanks for your help!

[Jimmy801]

Hello @webportal 

you can use the group-function and use this function for sum your grouped table, but apply before a date-filter.

 each List.Sum(Table.SelectRows(_,each  _[Date]>= Date.From(Date.AddYears(DateTime.FixedLocalNow(), -1)))[Sales])

 here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTK00DM00DMyADGVYnWggqZ6BiboggaGekCEJGgEEjTXM7DAEDTWM7DUM7REEQSZaQwXjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Date = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", Int64.Type}, {"Date",type date}, {"Sales", Int64.Type}}, "de-DE"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"SalesLast365Days", each List.Sum(Table.SelectRows(_,each  _[Date]>= Date.From(Date.AddYears(DateTime.FixedLocalNow(), -1)))[Sales]), type number}})
in
    #"Grouped Rows"

it transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy