Hello Members -
Question on Grouping / Merging of rows based on multiple columns and picking the latest date.
I have a direct Query model which has the Invoices and payments data. Trying to understand what is the best practice to design this model for the below scenario.
I need to show for a selected time period , what was the customer Ave days to pay an invoice.
Customer Invoice Days to Pay
2000 170000 27
2001 170001 51
Total Ave days based on number of invoices
Invoices Query - All the invoices by customers .
Payments - All payments received against the invoice. Includes multiple payments for single invoice.
The last receipt date to clear the invoice is the one to be used to consider the invoice cleared date.
Attaching the model. Example has 170001 invoice with multiple payments.
I was thinking of doing a Grouping by Invoice and then do a merge in power query. But how to Group the the rows and stamp the latest date against the payment.
Attaching the sample model. the model is actually based on direct Query, so i understand some of the date functions may not work for my model.