Hello all,
Looking for any help in figuring out how I can recreate the Total Open Amount results using DAX formula.
I’m quite new in using DAX so your help is much appreciated!
Cheers,
Kim
Hello all,
Looking for any help in figuring out how I can recreate the Total Open Amount results using DAX formula.
Cheers,
Kim
@KimDay,
You can use the following in a new calculated column:
Total Open Amount =
var __CurrentID = Table1[CustomerID]
VAR __CurrentInvoiceID = Table1[InvoiceID]
RETURN
CALCULATE(
SUM( Table1[Invoice Amt] ),
FILTER(
Table1,
Table1[CustomerID] = __CurrentID
)
)

CALCULATE(
SUM( Table1[Paid] ),
FILTER(
Table1,
Table1[CustomerID] = __CurrentID
&& Table1[InvoiceID] <= __CurrentInvoiceID
)
)
The first calculate is generating the grand total of the invoice amount by Customer ID.
The second calculate accounts for amounts paid. I went on the assumption that the invoiceID column is in order, meaning the later the Invoice ID the later that invoice took place. So if you look a this example, I added $50 to paid for customer 2 on their second invoice. And that 50 is accounted for when paid, and then total open amount after that is the 600  that 50.
You can do the same type of thing using measures for sure. But that would require more information and more setting up of the data model and such.
Another idea would be this as well if you wanted this in a measure, which I recommend
Try using this formula:
=CALCULATE( SUM( [Open Amount] ), ALL( Data[CustomerID] ) )
See if this gets you what you need as well.
Thanks
Sam