Filter All Except with two fields in different tables but only for the first date in selection

[Anonymous]

Hi All,

I have an interesting challenge. I have a field called opening balance which used filter all except and sums the amount for the first date in selection from the transactions table. Now I want to filter based on fund_id which is in the fund table. i can add a second all except but i need to keep the first date in selection (the 'Transactions'[Trx_Date] < MIN ( '[Trx_Date]) part. When i try to add it in the second all except it gives me an error. How do I add another all except but still filter both all excepts on the first date from the transactions table.

My current formula

Beginning Balance =

CALCULATE (

SUM ( 'relational COA_Transactions'[Trx_Amount_Adj] ),

FILTER ( ALLEXCEPT(('Transactions'),'Transactions'[COA L1 Code], Transactions'[Trx_Date] < MIN ( 'Transactions'[Trx_Date] )))

What I want to get

CALCULATE (

SUM ( 'relational COA_Transactions'[Trx_Amount_Adj] ),

FILTER ( ALLEXCEPT(('Transactions'),'Transactions'[COA L1 Code], Transactions'[Trx_Date] < MIN ( 'Transactions'[Trx_Date] )

,

ALLEXCEPT(('Fund),'Fund'[Fund_ID], Transactions'[Trx_Date] < MIN ( 'Transactions'[Trx_Date] )))

[Mariusz · Accepted solution]

Hi @Anonymous 

Providing Fund is a dimension that has a relationship with Transactions, you could write you expression like below.

Measure = 
VAR __minDate = MIN ( 'Transactions'[Trx_Date] )
RETURN 
CALCULATE (
    SUM ( 'relational COA_Transactions'[Trx_Amount_Adj] ),
    'Transactions'[Trx_Date] < __minDate,
    ALLEXCEPT( 'Transactions' ,'Transactions'[COA L1 Code], 'Fund'[Fund_ID] )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

Thanks Very Much Sir 🙂 It works great