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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Community Champion
Community Champion

  • 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
Community Champion
Community Champion

  • 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors