Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Average records before and after given date

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.

 

vehicle.JPG

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

  • 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"

View solution in original post

5 REPLIES 5
ronrsnfld
Super User
Super User

  • 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"
Anonymous
Not applicable

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},

 

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

Anonymous
Not applicable

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)!!


@Anonymous 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.

 


@Anonymous 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. 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.