Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
My aim is to produce a visual that will show customer payment days (ie how long it took them to pay the full invoice)
I have 2 tables. Invoices and Payments.
The invoices have a unique audit number.
The payments have an allocation column which may contain multiple invoice audit numbers.
The payments allocation column may also have one invoice audit number against multiple payments (if there has been part payments).
Payments:
Invoices:
Formula:
let InvAuditNo=[AutoIdx]
in
Text.Combine(
Table.SelectRows(CustomerPayments,
each Text.Contains(InvAuditNo,[cAllocs]))
[TxDate]
,
",")
As you can see, I am trying to bring in the TxDate from the payments table into the invoices table.
The PaymentDate column gives me an error - is this because there may be multiple payment dates or the column I am brinbging in is a date?
How can I change this formula to bring in the last date that was allocated to the invoice and no date if there has been no payment yet?
Kind regards
R
Solved! Go to Solution.
let InvAuditNo=[AutoIdx]
in
List.Max(
Table.SelectRows(CustomerPayments,
each Text.Contains([cAllocs],InvAuditNo))
[TxDate]
)
Perfect, Thank you SO much 🙂
let InvAuditNo=[AutoIdx]
in
List.Max(
Table.SelectRows(CustomerPayments,
each Text.Contains([cAllocs],InvAuditNo))
[TxDate]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.