Average records before and after given date

[samahiji]

Hi

I have the table attached and I want to calculate the average cost for 3 repairs (if available) before and after last repair date.

Note:  lastest repair date is in another table than needs to be filled down here.

[ronrsnfld · Accepted solution]

• Entries are assumed to be in date order
  • Sort if not
• Join the two tables to get the Repair Date associated with each Car
• Group by Car
  • Select Rows for either before or after the repair date
  • Average the Last 3 or the First 3 (assumes entries are in date order

Read code comments

let

//Read in both tables
//  and remove blank rows
// Change "Source" lines to reflect your actual data sources
    Source = Excel.CurrentWorkbook(){[Name="Output"]}[Content],
    #"Output Table"= Table.TransformColumnTypes(Source,{{"Car", type text}, {"Repair Date", type date}}),

    Source2 = Excel.CurrentWorkbook(){[Name="Repairs"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source2,{{"Car", type text}, {"Failure Date", type date}, {"Repair Cost", Int64.Type}}),

    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

//merge with output table and extract Repair Date column for car type
    Join = Table.NestedJoin(#"Changed Type","Car",#"Output Table","Car","Join",JoinKind.RightOuter),
    #"Expanded Join" = Table.ExpandTableColumn(Join, "Join", {"Repair Date"}, {"Repair Date"}),

//Group by car and compute averages
    #"Grouped Rows" = Table.Group(#"Expanded Join", {"Car"}, {
        
        {"Repair Date", each [Repair Date]{0}, type date},

        {"avg 3 repairs cost before",  (t)=> 
            List.Average(
                List.LastN(
                    Table.SelectRows(t, each [Failure Date] < [Repair Date])[Repair Cost],
                3)
            ), type number},

        {"avg 3 repairs cost after",  (t)=> 
            List.Average(
                List.FirstN(
                    Table.SelectRows(t, each [Failure Date] > [Repair Date])[Repair Cost],
                3)
            ), type number}    
       })
in
    #"Grouped Rows"

[samahiji]

ronrsnfld

In addition to Repair cost, If more costs are needed (i.e, insured cost, shipment cost as an additional column), how the codes look like?

something like this...

        {"avg 3 repairs cost before",  (t)=> 
            List.Average(
                List.LastN(
                    Table.SelectRows(t, each [Failure Date] < [Repair Date])[Repair Cost][Insured cost][Shipment cost],
                3)
            ), type number},

[ronrsnfld]

You will need a separate aggregation for each item you want a separate column/summary. 

[samahiji]

What the code will look like? 

also, I had an issue when two queries are merged, and then expand the table, the average numbers were changed (Seems multiply by 3)!!

[ronrsnfld]

---

@samahiji wrote:

What the code will look like? 

Just repeat each pair of aggregations ("average 3 costs before"; "average 3 costs after") for each column you wish to see that information.

---

@samahiji wrote:

also, I had an issue when two queries are merged, and then expand the table, the average numbers were changed (Seems multiply by 3)!!

---

I have no idea what you are referring to. The last step in the code I provided has nothing to be expanded.