Sales N-1 Month and calculation row by row

[Anonymous]

Hi Guys,

I have a query wich give me the Stock and the end of the month (column E - picture below) and the sales of that specific month (column F). The query is basically the columns B to F. 

However, i need to insert an extra column of the sales of the previous period (column G) and an average sales on the columns I (average of column F and G). 

I was thinking make it with measures in order to use the time intellligence. But my problem is that i always need to have the info row by row. And why? The column I (picture below) - stock rotation - its a division between the column H and column F. This column, will then be used to make a report. 

On this column:

I will make something like: 

If column I > 50 days; "stock rotation OK"

If column I < 49 days; "stock rotation NOK"

Accorging with this, do you have any tips in dax or power query to solve this?
Link below with the excel:

https://drive.google.com/file/d/1DgYyzuNe5Kq-d6iE_mh2A-hmjyw-33tD/view?usp=sharing

[Anonymous · Accepted solution]

@Anonymous  My apologies. In order to get more accurate on my doubts, i google it and now i found the way to change my source. That part is overpast.

Going forward:

I got your logical of the index and the sort by the key. I look at the result and seemed ok. But now i found the devil (that is always on the details ).

When we have a data list that its not mont n; month n+1,month n+2 (see picture below when we have 01/08/2015 and the next row is 4 months later, the file doesnt work. 

and why? see the following results. on the month 12 of 2015, we get the sales of the previous period (that should be null because we dont have any record on month 11 of 2015) as the sales of the month8 of 2015,

Link with the table of the refered example:

https://drive.google.com/file/d/1rBX54ZmBUt_iPRVmX_SDzGyLNEDF-DoA/view?usp=sharing

According with this, is there any chance to get over it?

Thank you!

[Anonymous]

this code after read the second point (I didn't read before)

let

    addCols=(Source)=>
    let
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Date", "Material", "Store", "Index", "Stocks end #(lf)month", "Sales #(lf)month"}),
    //#"Added Custom" = Table.AddColumn(#"Reordered Columns", "sale prev month", each try #"Reordered Columns"[#"Sales #(lf)month"]{[Index]-1} otherwise null),
    
    ac_1 = Table.AddColumn(#"Reordered Columns", "sale month_1", each try  (if  Date.AddMonths(#"Reordered Columns"[Date]{[Index]-1} ,1) =#"Reordered Columns"[Date]{[Index]} then #"Reordered Columns"[#"Sales #(lf)month"]{[Index]-1} else null)otherwise null ),
    ac_2 = Table.AddColumn(ac_1, "sale month_2", each try  (if  Date.AddMonths(ac_1[Date]{[Index]-2} ,2) =ac_1[Date]{[Index]} then ac_1[#"Sales #(lf)month"]{[Index]-2} else null)otherwise null ),
    av_2= Table.AddColumn(ac_2, "avg_2", each ([#"Sales #(lf)month"]+[sale month_1]+[sale month_2])/3),
    sr_2 = Table.AddColumn(av_2, "Stock Rotation Rate 2", each [#"Stocks end #(lf)month"]/[avg_2]*30)
in
    Table.ReplaceValue(sr_2,Number.PositiveInfinity,"no sales",Replacer.ReplaceValue,{"Stock Rotation Rate 2"}),





    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZHbDcMgDEV34TtS/aTpEJ0gyv5rxEAdKQkuSID5OLo2h21LgC9bBKhpSR8AQhG7fQmyFQSwUyHtS0MpRG8ghyBDpR2UP83b4aSGpNbtXJ5t/Z5uvYakXBL7JtEK3RL7ItGHpJHJQsqMSXy+uy8S/RMHIutrJjx6Hg8sngPymYixHa1kyzSwcLmbWEYk+nEUctc8HuftBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Material = _t, Store = _t, #"Stocks end #(lf)month" = _t, #"Sales #(lf)month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Material", Int64.Type}, {"Store", type text}, {"Stocks end #(lf)month", Int64.Type}, {"Sales #(lf)month", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Material", "Store"}, {{"all", each addCols(_) }}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "Index", "Stocks end #(lf)month", "Sales #(lf)month", "sale month_1", "sale month_2", "avg_2", "Stock Rotation Rate 2"}, {"Date", "Index", "Stocks end #(lf)month", "Sales #(lf)month", "sale month_1", "sale month_2", "avg_2", "Stock Rotation Rate 2"})
in
    #"Expanded all"

third point) yes to manage weeks period you could use that function

[Anonymous]

Hi @Anonymous ,

Hope you are well!

For the second point i didn`t make myself clear. The goal is to have the code you written, but wihtout "sr2". Stock rotation rate. Basically withtout any calculated field, just with the sales of preivous months.

Basically is to del the:

in
    Table.ReplaceValue(sr_2,Number.PositiveInfinity,"no sales",Replacer.ReplaceValue,{"Stock Rotation Rate 2"}),

 And on the "expand all" remove also the fields related with the "sr2". Im trying it but i have the erro of the "in followed by the let". i try to removed it but wihtout sucess.

Thanks again!!

[Anonymous · Accepted solution]

let

    addCols=(Source)=>
    let
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Date", "Material", "Store", "Index", "Stocks end #(lf)month", "Sales #(lf)month"}),
    //#"Added Custom" = Table.AddColumn(#"Reordered Columns", "sale prev month", each try #"Reordered Columns"[#"Sales #(lf)month"]{[Index]-1} otherwise null),
    
    ac_1 = Table.AddColumn(#"Reordered Columns", "sale month_1", each try  (if  Date.AddMonths(#"Reordered Columns"[Date]{[Index]-1} ,1) =#"Reordered Columns"[Date]{[Index]} then #"Reordered Columns"[#"Sales #(lf)month"]{[Index]-1} else null)otherwise null ),
    ac_2 = Table.AddColumn(ac_1, "sale month_2", each try  (if  Date.AddMonths(ac_1[Date]{[Index]-2} ,2) =ac_1[Date]{[Index]} then ac_1[#"Sales #(lf)month"]{[Index]-2} else null)otherwise null )
//    av_2= Table.AddColumn(ac_2, "avg_2", each ([#"Sales #(lf)month"]+[sale month_1]+[sale month_2])/3),
//    sr_2 = Table.AddColumn(av_2, "Stock Rotation Rate 2", each [#"Stocks end #(lf)month"]/[avg_2]*30)
in
   ac_2,
 // Table.ReplaceValue(ac_2,Number.PositiveInfinity,"no sales",Replacer.ReplaceValue,{"Stock Rotation Rate 2"}),





    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZHbDcMgDEV34TtS/aTpEJ0gyv5rxEAdKQkuSID5OLo2h21LgC9bBKhpSR8AQhG7fQmyFQSwUyHtS0MpRG8ghyBDpR2UP83b4aSGpNbtXJ5t/Z5uvYakXBL7JtEK3RL7ItGHpJHJQsqMSXy+uy8S/RMHIutrJjx6Hg8sngPymYixHa1kyzSwcLmbWEYk+nEUctc8HuftBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Material = _t, Store = _t, #"Stocks end #(lf)month" = _t, #"Sales #(lf)month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Material", Int64.Type}, {"Store", type text}, {"Stocks end #(lf)month", Int64.Type}, {"Sales #(lf)month", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Material", "Store"}, {{"all", each addCols(_) }}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "Index", "Stocks end #(lf)month", "Sales #(lf)month", "sale month_1", "sale month_2"}, {"Date", "Index", "Stocks end #(lf)month", "Sales #(lf)month", "sale month_1", "sale month_2"})
in
    #"Expanded all"

[Anonymous]

perhaps for those who are familiar with the jargon and commercial issues it is all clear.
But for me it is not and therefore I am not sure that what I did corresponds to what you expect.
I send the link of your excel file in which I added the formulas to calculate the following measures:
column G) sum of previous months' sales;
column H) average sales previous months;
column I) H / E

https://giga.allegati.tim.it/r?d=alice.it&wr=rocco.lupoi@telecomitalia.it&ws=sprmnt21&e=alice.it&c=O...

[Anonymous]

Hi Rocco,

Thank you for the reply. According with your answer i realiza that i wasnt clear enough. 😃 

I sent a sample in excel, however this exercice its for millions of rows (database in acess). It can`t be donne by excel formulas.

The sales on the previous month is something like the calculate formula with a dateadd, -1, month in dax. But to be donne in a report format. 

[Anonymous]

I can apply the same logic in power query if logic and power query are fine for you (I don't know anything about DAX).
Could you, in case if what I did on the excel sample is not what you expect, create a table with the expected result?

[Anonymous]

Well its just fine. Im looking for the result.

I sent you my logical on an "excel mindset". 

Assumptions:

You have an unique combination of month & material & store (column F & G & H), wich was created on the column E.

So i use this combination to get the previous month key = month -1 & material & store (column D). To get the "month-1" i used the sheet "Previous Month". Well i use that logical but i hate it. Its not easy and dont use the power of the dates formulas, however its the way i do in excel.

Again:

If the material "9002144" on the store "M206" has sales of:

Month 2= 50 units

Month 3 = 0 units

The result, on the month 3 should be:

If its possible to automate this, use the data concept without making "keys" and use it for millions of rows, it will be just AMAZING!

Link with this logical here:

https://drive.google.com/file/d/1OgbaZqoelrF1pO8IeERUkDgAeVHF1lic/view?usp=sharing

[Anonymous]

try and let's know

let

    addCols=(Source)=>
    let
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Date", "Material", "Store", "Index", "Stocks end #(lf)month", "Sales #(lf)month"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "sale prev month", each try #"Reordered Columns"[#"Sales #(lf)month"]{[Index]-1} otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "average", each ([#"Sales #(lf)month"]+[sale prev month])/2),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Stock Rotation Rate", each [#"Stocks end #(lf)month"]/[average]*30)
in
    Table.ReplaceValue(#"Added Custom2",Number.PositiveInfinity,"no sales",Replacer.ReplaceValue,{ "Stock Rotation Rate"}),





    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZHbDYAgDEV34ZvE21J8DOEEhv3XsGJqIoKQQMvHSR+H43CgSQ+DovNuA5hE9LUzZk0EaIxwyd8oN9ECDE0wINMGyk/zOxgZm2TM17h5tPUy3HptkvKqWDdJmrioWBdJNiT3TF6kjJik7951kWSf2BGZtxnwaPVCx+IzoIaUTg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Material = _t, Store = _t, #"Stocks end #(lf)month" = _t, #"Sales #(lf)month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Material", Int64.Type}, {"Store", type text}, {"Stocks end #(lf)month", Int64.Type}, {"Sales #(lf)month", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Material", "Store"}, {{"all", each addCols(_) }}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "Index", "Stocks end #(lf)month", "Sales #(lf)month", "sale prev month", "average", "Stock Rotation Rate"}, {"Date", "Index", "Stocks end #(lf)month", "Sales #(lf)month", "sale prev month", "average", "Stock Rotation Rate"})
in
    #"Expanded all"

[Anonymous]

Hi again Rocco,

Thank again for your help!!

In ordet to simulate better the conditions of the model, i was trying to insert a new source of data in your code. I used a Acess file called "Acess Hope".

/// 

let
Origem = Access.Database(File.Contents("C:\Users\HP\Desktop\Access Hope.accdb"), [CreateNavigationProperties=true]),
_Hope = Origem{[Schema="",Item="Hope"]}[Data]
in
_Hope

//

Then i was trying to find in your code where i can replace it. Where it is? Probably this is a very beginner question, but (till now) is where i am. My apologies for that.

I try to replace here "

 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZHbDYAgDEV34ZvE21J8DOEEhv3XsGJqIoKQQMvHSR+H43CgSQ+DovNuA5hE9LUzZk0EaIxwyd8oN9ECDE0wINMGyk/zOxgZm2TM17h5tPUy3HptkvKqWDdJmrioWBdJNiT3TF6kjJik7951kWSf2BGZtxnwaPVCx+IzoIaUTg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Material = _t, Store = _t, #"Stocks end #(lf)month" = _t, #"Sales #(lf)month" = _t]),

But didn`t work.

Thank you again!

[Anonymous]

to make the help you can receive more effective you need to explain in detail what you tried to do and where and what kind of error you got.

"It doesn't work" it doesn't work!

PS
You must provide at least screenshots of errors and the code and data you used

[Anonymous · Accepted solution]

@Anonymous  My apologies. In order to get more accurate on my doubts, i google it and now i found the way to change my source. That part is overpast.

Going forward:

I got your logical of the index and the sort by the key. I look at the result and seemed ok. But now i found the devil (that is always on the details ).

When we have a data list that its not mont n; month n+1,month n+2 (see picture below when we have 01/08/2015 and the next row is 4 months later, the file doesnt work. 

and why? see the following results. on the month 12 of 2015, we get the sales of the previous period (that should be null because we dont have any record on month 11 of 2015) as the sales of the month8 of 2015,

Link with the table of the refered example:

https://drive.google.com/file/d/1rBX54ZmBUt_iPRVmX_SDzGyLNEDF-DoA/view?usp=sharing

According with this, is there any chance to get over it?

Thank you!

[Anonymous]

there is some chance of making it 🙂

Try this

let

    addCols=(Source)=>
    let
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Date", "Material", "Store", "Index", "Stocks end #(lf)month", "Sales #(lf)month"}),
    //#"Added Custom" = Table.AddColumn(#"Reordered Columns", "sale prev month", each try #"Reordered Columns"[#"Sales #(lf)month"]{[Index]-1} otherwise null),
    
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "sale prev month", each try  (if  Date.AddMonths(#"Reordered Columns"[Date]{[Index]-1} ,1) =#"Reordered Columns"[Date]{[Index]} then #"Reordered Columns"[#"Sales #(lf)month"]{[Index]-1} else null)otherwise null ),
    
    
    
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "average", each ([#"Sales #(lf)month"]+[sale prev month])/2),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Stock Rotation Rate", each [#"Stocks end #(lf)month"]/[average]*30)
in
    Table.ReplaceValue(#"Added Custom2",Number.PositiveInfinity,"no sales",Replacer.ReplaceValue,{ "Stock Rotation Rate"}),





    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFRDsMgCIbvwnOTAYLdDrETNN7/GkUNTdrJNFHx4csPfh4HIL1sMZLCBh9EJhG7fRmzFUK0UxHK1lEO0QeYQjBhox2UP8374aSGpLbtXF5tvS+3foek3BLHJskKPxLHIsmH5JnJSsqKSfp991gk+SdORLbXLHj0vDSxeA2YrkSK7Wgje6aBlcvDxDoiM5RyAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Material = _t, Store = _t, #"Stocks end #(lf)month" = _t, #"Sales #(lf)month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Material", Int64.Type}, {"Store", type text}, {"Stocks end #(lf)month", Int64.Type}, {"Sales #(lf)month", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Material", "Store"}, {{"all", each addCols(_) }}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "Index", "Stocks end #(lf)month", "Sales #(lf)month", "sale prev month", "average", "Stock Rotation Rate"}, {"Date", "Index", "Stocks end #(lf)month", "Sales #(lf)month", "sale prev month", "average", "Stock Rotation Rate"})
in
    #"Expanded all"

[Anonymous]

Hi @Anonymous ,

Now it works. You rock! 

In the meanwhile i have spending some hours (im learnign so im quite slow..) in order to understang/change your code. I have to be able to edit slighly your code, otherwise i will be stuked with just smal details.

So i try to edit the code to change the source and change the name of the columns and i was sucedd. Cool. But now the problems arrived.

I try to make another 2 modifications and i wasnt suceed.

First situation: I tried to add another column with the sales of the month n-2 (beside the n-1) [see red circle on the image below]. At the same time, the average i try to edit in order to be the average of [#"Sales"]+[sale prev month]+[sale prev month 2]].

The code that i used was (the modifications are in "//coments"

let



    addCols=(Source)=>

    let

    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),

    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Date", "Material", "Store", "Index", "Stock", "Sales"}),

   

    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "sale prev month", each try  (if  Date.AddMonths(#"Reordered Columns"[Date]{[Index]-1} ,1) =#"Reordered Columns"[Date]{[Index]} then #"Reordered Columns"[#"Sales"]{[Index]-1} else null)otherwise null ),

   

    // this one was added:

    #"Added Custom3" = Table.AddColumn(#"Reordered Columns", "sale prev month 2", each try  (if  Date.AddMonths(#"Reordered Columns"[Date]{[Index]-2} ,1) =#"Reordered Columns"[Date]{[Index]} then #"Reordered Columns"[#"Sales"]{[Index]-2} else null)otherwise null ),



    // the "average" its suposed to be of "#sales, sales prev month, sale prev month 2"

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "average", each ([#"Sales"]+[sale prev month]+[sale prev month 2])/2),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Stock Rotation Rate", each [#"Stock"]/[average]*30)

in

    Table.ReplaceValue(#"Added Custom2",Number.PositiveInfinity,"no sales",Replacer.ReplaceValue,{ "Stock Rotation Rate"}),







    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Material", Int64.Type}, {"Store", type text}, {"Stock", Int64.Type}, {"Sales", Int64.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Material", "Store"}, {{"all", each addCols(_) }}),



 // also added the column created "sale prev month 2" here

    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "Index", "Stock", "Sales", "sale prev month", "sale prev month 2", "average", "Stock Rotation Rate"}, {"Date", "Index", "Stock", "Sales", "sale prev month", "sale prev month 2", "average", "Stock Rotation Rate"})

in

    #"Expanded all"

If it easier, Is on the link https://drive.google.com/file/d/1aMtBi1_u8TQH5-iX1Ah888BCSwuDDxyI/view?usp=sharing

Could you kindly look what is wrong, or, if its easier, is it possible to add that change at the code wihtout looking at my modifications?

Second situation: I tried to eliminate from the code the average ( #"Added Custom1") and the stock coverage rate (#"Added Custom2") columsn as well as on the final ouput, although i wasnt sucedd. Is it possible to remove it from the code? Probably for your takes a second 

Third situations: In order to adapt the logical from months to weeks, the code is able to chage it frim "Date.AddMonths" to "Date.AddWeeks", right? 

 #"Added Custom" = Table.AddColumn(#"Reordered Columns", "sale prev month", each try  (if  Date.AddMonths(#"Reordered Columns"[Date]{[Index]-1} ,1) =#"Reordered Columns"[Date]{[Index]} then #"Reordered Columns"[#"Sales"]{[Index]-1} else null)otherwise null ),

Thank you again for the help. My life at work will just changed! Thank you so much!!!!!

[Anonymous]

I haven't looked at your code, because it's hard to fix other people's code :-).
See if this code does what you ask (I changed the formula of the average by three months (m0 + m1 + m2) / 3) and try to figure out for yourself what is wrong with your code

let

    addCols=(Source)=>
    let
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Date", "Material", "Store", "Index", "Stocks end #(lf)month", "Sales #(lf)month"}),
    //#"Added Custom" = Table.AddColumn(#"Reordered Columns", "sale prev month", each try #"Reordered Columns"[#"Sales #(lf)month"]{[Index]-1} otherwise null),
    
    ac_1 = Table.AddColumn(#"Reordered Columns", "sale month_1", each try  (if  Date.AddMonths(#"Reordered Columns"[Date]{[Index]-1} ,1) =#"Reordered Columns"[Date]{[Index]} then #"Reordered Columns"[#"Sales #(lf)month"]{[Index]-1} else null)otherwise null ),
    ac_2 = Table.AddColumn(ac_1, "sale month_2", each try  (if  Date.AddMonths(ac_1[Date]{[Index]-2} ,2) =ac_1[Date]{[Index]} then ac_1[#"Sales #(lf)month"]{[Index]-2} else null)otherwise null ),
    
    
    av_1 = Table.AddColumn(ac_2, "avg_1", each ([#"Sales #(lf)month"]+[sale month_1])/2),
    av_2= Table.AddColumn(av_1, "avg_2", each ([#"Sales #(lf)month"]+[sale month_1]+[sale month_2])/3),

    sr_1 = Table.AddColumn(av_2, "Stock Rotation Rate 1", each [#"Stocks end #(lf)month"]/[avg_1]*30),
    sr_2 = Table.AddColumn(sr_1, "Stock Rotation Rate 2", each [#"Stocks end #(lf)month"]/[avg_2]*30)
in
    Table.ReplaceValue(sr_2,Number.PositiveInfinity,"no sales",Replacer.ReplaceValue,{ "Stock Rotation Rate 1","Stock Rotation Rate 2"}),





    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZHbDcMgDEV34TtS/aTpEJ0gyv5rxEAdKQkuSID5OLo2h21LgC9bBKhpSR8AQhG7fQmyFQSwUyHtS0MpRG8ghyBDpR2UP83b4aSGpNbtXJ5t/Z5uvYakXBL7JtEK3RL7ItGHpJHJQsqMSXy+uy8S/RMHIutrJjx6Hg8sngPymYixHa1kyzSwcLmbWEYk+nEUctc8HuftBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Material = _t, Store = _t, #"Stocks end #(lf)month" = _t, #"Sales #(lf)month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Material", Int64.Type}, {"Store", type text}, {"Stocks end #(lf)month", Int64.Type}, {"Sales #(lf)month", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Material", "Store"}, {{"all", each addCols(_) }}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "Material", "Store", "Index", "Stocks end #(lf)month", "Sales #(lf)month", "sale month_1", "sale month_2", "avg_1", "avg_2", "Stock Rotation Rate 1", "Stock Rotation Rate 2"}, {"all.Date", "all.Material", "all.Store", "all.Index", "all.Stocks end #(lf)month", "all.Sales #(lf)month", "all.sale month_1", "all.sale month_2", "all.avg_1", "all.avg_2", "all.Stock Rotation Rate 1", "all.Stock Rotation Rate 2"})
in
    #"Expanded all"

[Anonymous]

Hi @Anonymous ,

Now it works. You rock! 

In the meanwhile i have spending some hours (im learnign so im quite slow..) in order to understang/change your code. I have to be able to edit slighly your code, otherwise i will be stuked with just smal details.

So i try to edit the code to change the source and change the name of the columns and i was sucedd. Cool. But now the problems arrived.

I try to make another 2 modifications and i wasnt suceed.

First situation: I tried to add another column with the sales of the month n-2 (beside the n-1) [see red circle on the image below]. At the same time, the average i try to edit in order to be the average of [#"Sales"]+[sale prev month]+[sale prev month 2]].

 

The code that i used was (the modifications are in "//coments"

let

    addCols=(Source)=>
    let
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Date", "Material", "Store", "Index", "Stock", "Sales"}),
    
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "sale prev month", each try  (if  Date.AddMonths(#"Reordered Columns"[Date]{[Index]-1} ,1) =#"Reordered Columns"[Date]{[Index]} then #"Reordered Columns"[#"Sales"]{[Index]-1} else null)otherwise null ),
    
    // this one was added:
    #"Added Custom3" = Table.AddColumn(#"Reordered Columns", "sale prev month 2", each try  (if  Date.AddMonths(#"Reordered Columns"[Date]{[Index]-2} ,1) =#"Reordered Columns"[Date]{[Index]} then #"Reordered Columns"[#"Sales"]{[Index]-2} else null)otherwise null ), 

    // the "average" its suposed to be of "#sales, sales prev month, sale prev month 2"
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "average", each ([#"Sales"]+[sale prev month]+[sale prev month 2])/2),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Stock Rotation Rate", each [#"Stock"]/[average]*30)
in
    Table.ReplaceValue(#"Added Custom2",Number.PositiveInfinity,"no sales",Replacer.ReplaceValue,{ "Stock Rotation Rate"}),



    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Material", Int64.Type}, {"Store", type text}, {"Stock", Int64.Type}, {"Sales", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Material", "Store"}, {{"all", each addCols(_) }}),
 
 // also added the column created "sale prev month 2" here
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "Index", "Stock", "Sales", "sale prev month", "sale prev month 2", "average", "Stock Rotation Rate"}, {"Date", "Index", "Stock", "Sales", "sale prev month", "sale prev month 2", "average", "Stock Rotation Rate"})
in
    #"Expanded all"

If it easier, Is on the link https://drive.google.com/file/d/1aMtBi1_u8TQH5-iX1Ah888BCSwuDDxyI/view?usp=sharing

Could you kindly look what is wrong, or, if its easier, is it possible to add that change at the code wihtout looking at my modifications?

Second situation: I tried to eliminate from the code the average ( #"Added Custom1") and the stock coverage rate (#"Added Custom2") columsn as well as on the final ouput, although i wasnt sucedd. Is it possible to remove it from the code? Probably for your takes a second 😅

Third situations: In order to adapt the logical from months to weeks, the code is able to chage it frim "Date.AddMonths" to "Date.AddWeeks", right? 

 #"Added Custom" = Table.AddColumn(#"Reordered Columns", "sale prev month", each try  (if  Date.AddMonths(#"Reordered Columns"[Date]{[Index]-1} ,1) =#"Reordered Columns"[Date]{[Index]} then #"Reordered Columns"[#"Sales"]{[Index]-1} else null)otherwise null ),

Thank you again for the help. My life at work will just changed! Thank you so much!!!!!