Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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
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.
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
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.
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
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?
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
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.
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!
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