Summarizing table sales

[Anonymous]

I have order table like 

CustomerId	OrderAmount	Orderdatetime	OrderAmountCurrency
AAA	120	02/03/2022 02:03	US$120
AAA	20	02/03/2022 02:20	US$20
AAA	320	03/03/2022 03:03	US$320
BBB	300	02/03/2022 02:03	$300
BBB	20	02/03/2022 02:20	$20
BBB	200	02/03/2022 03:03	$200

I'd like to display a table like this: Customerid, initial order-The amount of the first attempt All purchases worth less than $ 40 should be excluded, Ordedate-the order of the first attempt; Final Order- the lastest attempt

CustomerId	InitialOrder	Orderdate	FinalOrder
AAA	US$120	02/03/2022 02:03	US$320
BBB	$300	02/03/2022 02:03	$200

How to do it?

[AlexisOlson · Accepted solution]

You can do this in a table visual like this:

The InitialOrder and FinalOrder measures are defined like this:

FinalOrder = 
VAR OrderDate = MAX ('Table'[Orderdatetime] ) /*Use MIN instead for InitialOrder*/
RETURN
    CALCULATE (
        SELECTEDVALUE ('Table'[OrderAmountCurrency] ),
        'Table'[Orderdatetime] = OrderDate
    )

[sanalytics]

@Anonymous 

Below is the PQ solution

Main Output

let
Source = Data,
#"Filtered Rows" = Table.SelectRows(Source, each ([OrderAmount] <> "20")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"CustomerId"}, {{"Count", each List.Min([Orderdatetime]), type nullable datetime}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"CustomerId", "Count"}, Data, {"CustomerId", "Orderdatetime"}, "Data", JoinKind.LeftOuter),
#"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"OrderAmountCurrency"}, {"Data.OrderAmountCurrency"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Data", {"CustomerId"}, #"Data (3)", {"CustomerId"}, "Data (3)", JoinKind.LeftOuter),
#"Expanded Data (3)" = Table.ExpandTableColumn(#"Merged Queries1", "Data (3)", {"Data.OrderAmountCurrency"}, {"Data (3).Data.OrderAmountCurrency"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Data (3)",{{"Count", "Orderdate"}, {"Data.OrderAmountCurrency", "Initial Order"}, {"Data (3).Data.OrderAmountCurrency", "Final Order"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"CustomerId", "Initial Order", "Orderdate", "Final Order"})
in
#"Reordered Columns"

For Data (3)
let
Source = Data,
#"Filtered Rows" = Table.SelectRows(Source, each ([OrderAmount] <> "20")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"CustomerId"}, {{"Count", each List.Max([Orderdatetime]), type nullable datetime}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"CustomerId", "Count"}, Data, {"CustomerId", "Orderdatetime"}, "Data", JoinKind.LeftOuter),
#"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"OrderAmountCurrency"}, {"Data.OrderAmountCurrency"})
in
#"Expanded Data"

Please note that, the above table can be achieved in DAX also..I did it power query quickly for time constraints.May be later, I will send you the DAX solution

Hope this will help you

Regards
sanalytics

if it is your solution then please like and accept it as your solution