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
2 queries
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.
Hi @train, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Hi @train, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
