Calculating Average of next three or four months based on a criteria on any given day of the year.

[SuprajaChennuru]

I need to create a table , which can give me Quantity information based on criteria below. 

I have an existing Table, Table  A with information like below. Please note below is just a sample set of my data. The original data has information from 2022 December to 2025 December

Item	Type	Date	Quantity
A	S	12/01/2023	100
A	S	01/01/2024	200
A	S	02/01/2024	300
A	S	04/01/2024	400
A	S	05/01/2024	500
B	P	12/01/2023	100
B	P	01/01/2024	200
B	P	02/01/2023	300
B	P	04/01/2024	400
B	P	05/01/2024	500
B	P	06/01/2024	600

I would like to calculate New Quantity, where at any given time, 

Type = S then Quantity = average of Quantity for the next Four months 

(That is, if I am in January my New Quantity for S type items would be (Feb+Mar+Apr+May/)4

Type = P then Quantity = average of Quantity for the next Three months 

(That is, if I am in January my New Quantity for P type items would be (Feb+Mar+Apr)/3

So, now, my new table should look something like below:

Item	Type	Date	Quantity	New Quantity
A	S	12/01/2023	100	350
A	S	01/01/2023	200
A	S	02/01/2023	300
A	S	04/01/2023	400
A	S	05/01/2023	500
B	P	12/01/2023	100	300
B	P	01/01/2023	200	400
B	P	02/01/2023	300	500
B	P	04/01/2023	400
B	P	05/01/2023	500
B	P	06/01/2023	600

[slorin]

Hi

another solution

let
Source = Your_Source,
Join = Table.NestedJoin(Source, {"Item"}, Source, {"Item"}, "Data", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Data", {"Date", "Quantity"}, {"Date.1", "Quantity.1"}),
Filter = Table.SelectRows(
       Expand, 
       each [Date.1]>[Date] and Date.AddMonths([Date],if [Type]="S" then 5 else 4)>=[Date.1]),
Group = Table.Group(
      Filter, 
      {"Item", "Type", "Date", "Quantity"},
      {{"New Quantity", 
          each if ([Type]{0}="S" and Table.RowCount(_)=4) or ([Type]{0}="P" and Table.RowCount(_)=3) 
               then List.Average([Quantity.1]) 
               else null, type number}})

in
Group

 Stéphane

[AlienSx]

@SuprajaChennuru 

let
    Source = your_table,
    m = [S = 4, P = 3],
    f2 = (lst, off, count) =>
        [a = List.Range(lst, off, count),
        b = List.Count(a), 
        c = if b = count then List.Average(a) else null][c], 
    f = (tbl) =>
        [c = Table.RowCount(tbl),
        cols = Table.ToColumns(tbl),
        q = tbl[Quantity],
        s = Record.FieldOrDefault(m, tbl[Type]{0}, 0),
        gen = List.Generate(
            () => [i = 0, r = f2(q, 1, s)],
            (x) => x[i] < c,
            (x) => [i = x[i] + 1, r = f2(q, i + 1, s)],
            (x) => x[r]
        ),
        out = Table.FromColumns(cols & {gen}, Table.ColumnNames(tbl) & {"New Quantity"})][out],
    g = Table.Group(Source, {"Item", "Type"}, {{"rows", each f(Table.Sort(_, "Date"))}}),
    expand = Table.ExpandTableColumn(g, "rows", {"Date", "Quantity", "New Quantity"})
in
    expand

[ThxAlot]

It seems most novices are obssessed with PQ for calculations ... DAX is way more concise and elegant in your case.

[SuprajaChennuru · Accepted solution]

Thanks for suggesting an alternate route. After performing a series of group By operations and using the suggested calculation, I was able to reach the goal. 

Below is a sample of my output

[Kishore_Kadhir]

This question was posted under Power Query section. So, it was assumed that the author of this post wants it to be solved there. At times, there is more than one solution to a problem and I find nothing wrong in being obssessed with PQ. Hope you understand the aim of this community which is not to judge others but to help each other. 

[Kishore_Kadhir]

Hi @SuprajaChennuru 

Please open the advanced editor in Power Query and paste the following code.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc7BCcAwCEDRXTwnoMb03k5Q6DFk/zVioYJFW1BQ3uWPATsUuHSRKnFl5KYPIcIsL9RRFH04QTZsCTZDSVAM+4OH3udXkMMY5DEEeQxBHv+CsBtuN84F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Type = _t, Date = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Type", type text}, {"Date", type date}, {"Quantity", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"AllRows", each _, type table [Item=nullable text, Type=nullable text, Date=nullable date, Quantity=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Sort", each Table.Sort([AllRows], {"Date", Order.Ascending})),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "QuantityList", each Table.Column([AllRows], "Quantity")),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Type","Sort", "QuantityList"}),
#"Expanded Sort" = Table.ExpandTableColumn(#"Removed Other Columns", "Sort", {"Item", "Date", "Quantity"}, {"Item", "Date", "Quantity"}),
#"Grouped Rows1" = Table.Group(#"Expanded Sort", {"Type"}, {{"AllRows", each _, type table [Type=nullable text, Item=text, Date=date, Quantity=number, Custom=list]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "IndexedTable", each Table.AddIndexColumn([AllRows], "Index", 1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"}),
#"Added Custom4" = Table.AddColumn(#"Removed Columns", "TotalRowsInEachType", each Table.RowCount([IndexedTable])),
#"Expanded IndexedTable" = Table.ExpandTableColumn(#"Added Custom4", "IndexedTable", {"Item", "Date", "Quantity", "QuantityList", "Index"}, {"Item", "Date", "Quantity", "QuantityList", "Index"}),
#"Added Custom3" = Table.AddColumn(#"Expanded IndexedTable", "Average", each if([Type] = "S" and [TotalRowsInEachType] - [Index] >=4)
then List.Sum(List.FirstN(List.RemoveFirstN([QuantityList], [Index]), 4)) / 4
else if([Type] = "P" and [TotalRowsInEachType] - [Index] >=3)
then List.Sum(List.FirstN(List.RemoveFirstN([QuantityList], [Index]), 3)) / 3
else null),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"Type", "Item", "Date", "Quantity", "Average"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Average", type number}})
in
#"Changed Type1"

Thanks,

Kishore

[SuprajaChennuru]

Thanks for the response Kishore.  This helps mostly.                                                                                          But, In my case I have some repetition on date for the same item and it makes the average a null. i.e once the last "Add column" of "Average" is executed, all it gives me is "null" . Please see example Table below for the type of data I currently have.                                                                                                                                    For additional context, The data I have has total rows like below, once I used the Index column.                                                                                                          

Type	TotalRowsinEachType
S	525035
P	767869

Example Table: 

Item	Type	Date	Quantity
A	S	12/01/2023	100
A	S	12/01/2023	150
A	S	12/01/2023	120
A	S	01/01/2024	200
A	S	01/01/2024	250
A	S	01/01/2024	270
A	S	02/01/2024	300
A	S	02/01/2024	400
A	S	03/01/2024	500
B	P	12/01/2023	100
B	P	12/01/2023	200
B	P	01/01/2024	300
B	P	02/01/2024	400
B	P	03/01/2024	500
B	P	03/01/2024	600

[Kishore_Kadhir]

Can you try the below code and see if this is what you are expecting?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBRCsAgCIDhu/hcYGp7304w2GN0/2vMwQSHLihIvpc/x4AdClx6sdVGlZBYh4YIs3xQj6LoQAmSISfIhpKgGPYXD32ff0EOY5DHEOQxBHlcBWE33OJXXG1fbIgenDc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Type = _t, Date = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Type", type text}, {"Date", type date}, {"Quantity", Int64.Type}}),
#"Added Custom5" = Table.AddColumn(#"Changed Type", "YearMonth", each Number.ToText(Date.Year([Date])) &
Text.PadStart(Number.ToText(Date.Month([Date])), 2, "0")),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom5",{{"YearMonth", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type2", {"Type", "YearMonth"}, {{"AllRows", each _, type table [Item=nullable text, Type=nullable text, Date=nullable date, Quantity=nullable number, YearMonth=nullable number]}, {"Quantity", each List.Sum([Quantity]), type nullable number}}),
#"Grouped Rows2" = Table.Group(#"Grouped Rows", {"Type"}, {{"TotalRows", each _, type table [Type=nullable text, YearMonth=nullable number, AllRows=table, Quantity=nullable number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows2", "Sort", each Table.Sort([TotalRows], {"YearMonth", Order.Ascending})),
#"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 1, 1, Int64.Type),
#"Added Custom2" = Table.AddColumn(#"Added Index", "QuantityList", each #"Added Index"[Sort]{[Index] - 1}[Quantity]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Type","Sort", "QuantityList", "Index"}),
#"Expanded Sort2" = Table.ExpandTableColumn(#"Removed Other Columns", "Sort", {"YearMonth", "AllRows", "Quantity"}, {"YearMonth", "AllRows", "Quantity"}),
#"Grouped Rows1" = Table.Group(#"Expanded Sort2", {"Type"}, {{"AllRows", each _, type table [Type=nullable text, YearMonth=number, AllRows=table, Quantity=number, QuantityList=list, Index=number]}}),
#"Added Custom12" = Table.AddColumn(#"Grouped Rows1", "IndexedTable", each Table.AddIndexColumn([AllRows], "NewIndex", 1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom12",{"AllRows"}),
#"Added Custom4" = Table.AddColumn(#"Removed Columns", "TotalRowsInEachType", each Table.RowCount([IndexedTable])),
#"Expanded IndexedTable1" = Table.ExpandTableColumn(#"Added Custom4", "IndexedTable", {"YearMonth", "AllRows", "Quantity", "QuantityList", "Index", "NewIndex"}, {"YearMonth", "AllRows", "Quantity", "QuantityList", "Index", "NewIndex"}),
#"Added Custom3" = Table.AddColumn(#"Expanded IndexedTable1", "Average", each if([Type] = "S" and [TotalRowsInEachType] - [NewIndex] >=4)
then List.Sum(List.FirstN(List.RemoveFirstN([QuantityList], [NewIndex]), 4)) / 4
else if([Type] = "P" and [TotalRowsInEachType] - [NewIndex] >=3)
then List.Sum(List.FirstN(List.RemoveFirstN([QuantityList], [NewIndex]), 3)) / 3
else null),
#"Expanded AllRows1" = Table.ExpandTableColumn(#"Added Custom3", "AllRows", {"Item", "Date", "Quantity"}, {"Item", "Date", "Quantity.1"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded AllRows1"),
#"Removed Other Columns2" = Table.SelectColumns(#"Removed Duplicates",{"Type", "Item", "Date", "Quantity.1", "Average"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns2",{{"Quantity.1", "Quantity"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Average", type number}, {"Quantity", Int64.Type}, {"Date", type date}})
in
#"Changed Type1"

[SuprajaChennuru]

Thanks for the quick turnaround Kishore. But this also, endsup with same response as "Null" for Average column, even though I did not change any parameters.