Forum Discussion

ShubhamMudliar's avatar
ShubhamMudliar
New Member
2 years ago

DAX for computing two measures based on selected date

Hi all, I have a scenario which I am working on . Below is an export from POwer BI report. 

I two table : Effective date and transactions table. Invoice amount , collected amount and open balance are DAX measures.

Below are their formula:
1)InvoiceAmount:=
CALCULATE(SUM(FACTCustomerTransactions[AMOUNTMST]),
FILTER ( FACTCustomerTransactions, FACTCustomerTransactions[TransTypeName] <> "Write off"

),Transactions[Transdate]<=MAX(EffectiveDate[Date]))
2)Collected:=
CALCULATE(SUM(FACTCustomerTransactions[SETTLEAMOUNTMST]) + ( [PPDAmount] + [WriteOff]),
FACTCustomerTransactions, FACTCustomerTransactions[TransTypeName] <> "Write off",Transactions[Transdate]<=MAX(EffectiveDate[Date])

3) Open AR:=
CALCULATE (
[Balance]
, Transactions[TRANSDATE]<= MAX(EffectiveDate[Date])
) ------where Balance =Invoice amount -collected amount
I have an existing relationship between the two table. 
Now I  want to create a measure  as per below scenarios:

1) when user selects effective date after the due date (refer image) then the  [balance] should show invoice - collected

2) when user selects effective date between invoice date and duedate (refer image) then Collected should be zero as nothing get collected  before the due date . And that amount should get reflected in [balance].

Appreciate any help !!

2 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    2 years ago

    Hi,

    Here is one way to do this:

    Demo Data:

     

     

    Data model:

     

    Dax:

    Balance =
    var _t = SUM(Invoices[Total Amount])
     var _due = MAX(Invoices[Due Date])
     var _invoice = MAX(Invoices[Invoice Date])
     var _seldate = MAX('Calendar'[Date])
     var _id = MAX(Invoices[ID])
     RETURN
    _t -
    IF(_seldate<=_due && _seldate >=_invoice,_t,
    CALCULATE(SUM('Transaction'[Value]),ALL('Transaction'),'Transaction'[Date]<=_seldate,_id='Transaction'[ID])) //collected amount
    Here the last 2 rows are collected amount.


    End result:

     

     

     

    Here the conditions are fulfilled and when the selection changes the balance changes along.

    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/