How to DAX for a complex sql query like filter date first, then group by, then join other table

River  thanks for this.

 

I had a thorough look into it.

 

The measure I gave you generates this

 

But If we disect things, for the given date range, 

it returns following

 

 

On the other hand, if we disect Invoice table with exact same values it returns following

 

So you would have like a measure, which generates only SUM of 

'Invoice Paid'[SETTLEAMOUNTMST]

for only 'Invoice'[AccountNum]=3

 

Please change your measure to this

Amount Paid = 
CALCULATE (
    - SUM ( 'Invoice Paid'[SETTLEAMOUNTMST] ), --this gets sliced as per Date tbl, there is no need to explicitly
    // ALLEXCEPT (                             -- put a Date Filter
    //     'Invoice Paid',
    //     'Invoice Paid'[AccountNum],
    //     'Invoice Paid'[LastSettleVoucher],    --ALLEXCEPT is not required here cause 
    //     'Invoice Paid'[DataAreaId],           -- 'Invoice Paid'[AccountNum],'Invoice Paid'[LastSettleVoucher]
    //     'Invoice Paid'[Partition]             -- 'Invoice Paid'[DataAreaId],'Invoice Paid'[Partition]
    // ),                                        -- is explictly mentioned in TREATAS which generates the sum
    TREATAS (                                    -- as per that grouping
        SUMMARIZE (
            'Invoice',
            'Invoice'[AccountNum],
            'Invoice'[Voucher],
            'Invoice'[DataAreaId],
            'Invoice'[Partition]
        ),
        'Invoice Paid'[AccountNum],              
        'Invoice Paid'[LastSettleVoucher],
        'Invoice Paid'[DataAreaId],
        'Invoice Paid'[Partition]
    )
)