open amount between two dates

[Drobinson1]

I have two unrelated tables and want to calculate the total amount open if transactiondate<datetable[date] and dateallocated is >datetable[date]. Seems pretty simple but I cannot get it to work.

Example below is one transaction.  It was created 12/1/2016 and paid 1/12/2016. Therefore in the seperate date table it would show $25 for all of the days in Jan until it was paid on the 12th.

[CahabaData]

You need to relate the tables.  Based on what I see - there isn't a value to relate.  You could add an ID field to both tables and enter the same value on all rows i.e. 1 of the Date table and the single row of the Transaction table.  Then join the 2 tables on the ID field.  After that you can write DAX that will calculate as you seek.

[Drobinson1]

I am able to get this to work without an issue  for a given date:

ARSlicer = CALCULATE(SUM('Accounts Receivable'[Invoice Amount]),FILTER('Accounts Receivable','Accounts Receivable'[TRANSACTION_DATE]=AROpenDates[Date]))

But it seems to break if i try to do something like less than date:

ARSlicer = CALCULATE(SUM('Accounts Receivable'[Invoice Amount]),FILTER('Accounts Receivable','Accounts Receivable'[TRANSACTION_DATE]<AROpenDates[Date]))

[Eric_Zhang · Accepted solution]

@Drobinson1

Try

Open Amt =
CALCULATE (
    SUM ( transactiontable[Amount] ),
    FILTER (
        transactiontable,
        dateTable[Date Table] >= transactiontable[Transactiondate]
            && dateTable[Date Table] <= transactiontable[Date allocated]
    )
)