Forum Discussion
DAX Formula using Filter
Hello! I need help on this.
I have this matrix table. I have to get the difference confirmed invoices minus the invoices paid. Confirmed and Invoice Paid is a project invoice status under this field "invoicedetail[projectinvoicestatus]. Tried creating an DAX formula but it doesn't show the correct matrix that I want.
Outstanding Balance =
VAR _invoicepaid =
CALCULATE(
SUM('invoicedetail'[Amount]),
'invoicedetail'[invoice.msdyn_projectinvoicestatusname]
IN { "Invoice Paid" }
)
VAR _confirmed =
CALCULATE(
SUM('invoicedetail'[Amount]),
invoicedetail[invoice.msdyn_projectinvoicestatusname]
IN {"Confirmed"}
)
RETURN
_confirmed - _invoicepaid
I only want to show in the matrix the Confirmed Amount, Invoice Paid Amount and the Outstanding Balance.
Hope you could help me ont his.
Thank you in advance.
juhoneyighot , Try creating three mesures separately
DAX
Confirmed Amount =
CALCULATE(
SUM('invoicedetail'[Amount]),
'invoicedetail'[invoice.msdyn_projectinvoicestatusname] = "Confirmed"
)Invoice Paid Amount =
CALCULATE(
SUM('invoicedetail'[Amount]),
'invoicedetail'[invoice.msdyn_projectinvoicestatusname] = "Invoice Paid"
)Outstanding Balance =
[Confirmed Amount] - [Invoice Paid Amount]
3 Replies
- bhanu_gautamSuper User
juhoneyighot , Try creating three mesures separately
DAX
Confirmed Amount =
CALCULATE(
SUM('invoicedetail'[Amount]),
'invoicedetail'[invoice.msdyn_projectinvoicestatusname] = "Confirmed"
)Invoice Paid Amount =
CALCULATE(
SUM('invoicedetail'[Amount]),
'invoicedetail'[invoice.msdyn_projectinvoicestatusname] = "Invoice Paid"
)Outstanding Balance =
[Confirmed Amount] - [Invoice Paid Amount]- juhoneyighotHelper III
bhanu_gautam you mean I will make 3 measures on this?
- juhoneyighotHelper III
Yeah I thank this is correct. Create 3 measures. Thanks.