Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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/
Proud to be a Super User!
Hi
Getting this error
Try creating a measure based on the dax. For a column the syntax needs to be a bit different.
Proud to be a Super User!
I'm getting the same error
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.
It worked, Thank you so much
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |