latest transaction rows

Capstone

You are going to need to modify your table a little bit and add an additional 'flag' column in order to make the DAX really easy.

The modification is to be done in Power Query as per below. You just need to modify the Source step to link it to your real source:

let


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldRbisMwDAXQrQz5LlRXDz+0ldL9b6MxM32MBKb6MijixI6vcrsdoCvGlQnjuBxGhHPBc3k++mF2olQyHPfL1wSt/v8luPUCgenKSdW2I8KuMVwkqTIqRHfWpPKsHKQ5LKtUIcypZbVyI5D1+ZPKFYJdR1alQsBlZlV3RAgByCVe0qlagZjOcV/kvE1WEIYjmdimO7R3J4kkvYPZP4Dfw8Z2c2isaC8A6mSJpAJw/hJaIlEAsDYcSS4AtGY5kvIN0P/eJ2uUo6k7YQaB1yQn1CoE1iRHFa1CkL8C9lbpjMP9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, LocationID = _t, CustomerID = _t, StartTime = _t, EndTime = _t]),
    
    
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date},{"LocationID", Int64.Type}, {"CustomerID", Int64.Type}, {"StartTime", type datetime}, {"EndTime", type datetime}}),
    
    
   SortTable = Table.Sort( 
                    ChangedType, 
                    { 
                        {"LocationID", Order.Ascending}, 
                        {"StartTime", Order.Ascending} 
                    } ),
   
   GroupTable = Table.Group( 
                    SortTable, 
                    {"LocationID", "CustomerID"}, 
                    { 
                        //{ "MaxStartTime", each List.Max( [StartTime] ), type datetime },
                        {"FullTable", each _, type table }
                    },
                    GroupKind.Local
   ),
   
   ReGroupTable = Table.Group( GroupTable, {"LocationID"}, {"MaxDate", each List.Max( Table.Combine( [FullTable] )[StartTime] ), type table }, GroupKind.Global ),
   
   MergeTables = Table.NestedJoin( GroupTable, {"LocationID"}, ReGroupTable, {"LocationID"}, "MaxDateTable" ),
   
   AddFlag = Table.AddColumn( MergeTables, "Flag", each if List.Max( [FullTable][StartTime] ) = List.Max( [MaxDateTable][MaxDate] ) then true else false, type logical ),
   
   SelectCols = Table.SelectColumns(AddFlag,{"FullTable", "Flag"}),
   
   ExpandedFullTable = Table.ExpandTableColumn(SelectCols, "FullTable", {"Date", "LocationID", "CustomerID", "StartTime", "EndTime"}, {"Date", "LocationID", "CustomerID", "StartTime", "EndTime"}),
   
   Final = Table.TransformColumnTypes(ExpandedFullTable, {{"Date", type date}, {"LocationID", Int64.Type}, {"CustomerID", Int64.Type}, {"StartTime", type datetime}, {"EndTime", type datetime}})
   
   
in
    Final

Afterwards, I have normalized the LocationID and CustomerID and built the below model:

Then the dax becomes really easy:

Average Duration ( Minutes ) =
AVERAGEX (
    CALCULATETABLE ( Transactions, Transactions[Flag] = TRUE () ),
    ( Transactions[EndTime] - Transactions[StartTime] )
        * 1440
)

Let me know if you need me to share the .pbix