Forum Discussion
PowerBI
Hi Friends
Here we have two tables I need to join two tables. My requirement here is AccountingDate should be the next date of InvoiceDate but not the maximum date as follows
Table 1 - BillNo InvoiceDate
203 03-11-2017
204 04-12-2019
Table 2 - BillNo AccountingDate
203 28-09-2010
203 11-01-2017
203 03-11-2017
203 08-12-2020
203 29-09-2021
204 05-01-2020
204 07-08-2021
Output - BillNo InvoiceDate AccountingDate
203 03-11-2017 08-12-2020
204 04-12-2019 05-01-2020
Kindly help me to solve it. Thanks in advance
Hi Anonymous ，
Please try the following column:
AccDate = MINX ( FILTER ( AcntDate, AcntDate[BillNo] = Invoice[BillNo] && AcntDate[AccountingDate] > Invoice[InvoiceDate] ), AcntDate[AccountingDate] )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi,
Try something like this:
Accounting date =
var _bill = MAX(Table1[BillNo]) return
CALCULATE(MAX(Table2[Accountingdate]),ALL(Table2),Table2[BillNo]=_bill,Table2[Accountingdate]<MAX(Table2[Accountingdate]))
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/Try creating a measure based on the dax. For a column the syntax needs to be a bit different.
