Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 !!
Hi,
Here is one way to do this:
Demo Data:
Data model:
Dax:
Here the conditions are fulfilled and when the selection changes the balance changes along.
Proud to be a Super User!
Thanks for the solution. It worked!!
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |