PowerBI

[Anonymous]

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

[ValtteriN]

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/

[Anonymous]

Hi

Getting this error

[ValtteriN]

Try creating a measure based on the dax. For a column the syntax needs to be a bit different.

[Anonymous]

I'm getting the same error

[v-kkf-msft · Accepted solution]

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.

[Anonymous]

It worked, Thank you so much