cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## 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
Resident Rockstar
• 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
Resident Rockstar
• 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"``````
Helper I

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

Resident Rockstar

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

Helper I

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

Resident Rockstar

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors