Forum Discussion

juhoneyighot's avatar
juhoneyighot
Helper III
2 years ago
Solved

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

  • 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]