cancel
Showing results 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

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.

1 ACCEPTED SOLUTION
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

``````let

//  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"``````
5 REPLIES 5
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

``````let

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

Super User

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

Super User

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

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors