DAX for computing two measures based on selected date

[ShubhamMudliar]

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 !!

[ValtteriN]

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/

[ShubhamMudliar]

Thanks for the solution. It worked!!