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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors