Dax Measure to calculate open balance

[ataladayFBG]

I'm trying to calculate an open balance and having issues.

I have the AR table which has invoice level detail with the date entered, payment date, and amount.  I want to show a table by date with the outstanding balance.

Here is a simplified version of my AR table:

This is what I want - the excel formula is =SUMIFS(Amount,Date Entered,<=Date,Date Paid,>Date)

or sum the amount if the date shown is greater than or equal to the date entered AND is less than the date paid.  Add complication in the real source data has blanks in the date paid column for invoices not paid yet.

There is a link between the AR table and the date table with a column called 'AR'[Date Key] and 'Calendar'[Date] - the date key is the date entered except in situations where that is blank.

I've tried using this formula: 

Oustanding Balance =

Var MaxDate = max('Calendar'[Date])

Return

calculate(sumx('AR','AR'[Amount]),all('Calendar'),all('AR'[DateKey]),filter(all('AR'[Payment date]),'AR'[Payment date] = blank() || 'AR'[Payment date] > MaxDate),filter(all('AR'[Date entered]),'AR'[Date entered]<=MaxDate))

It shows the value starting at the date entered, but then the value doesn't drop off when the payment was made.

What's the best way to write the expression?

[Anonymous · Accepted solution]

Hi @ataladayFBG ,

The following DAX might work for you:

Open Amount = 
CALCULATE(
    SUM(AR[Amount]),
    FILTER(
        'AR',
        'AR'[Date Entered] <= 'Summary'[Date] &&
        'AR'[Date Paid] >= 'Summary'[Date]
    )
)

And the final output is shown in the following figure:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ataladayFBG]

I've also tried creating 2 measures, 1 for the cummulative amount and 1 for the cummulative paid, and then I could just subtract the 2.  Cummulative amount works fine.  I put an inactive relationship between date paid and the calendar date and tried the following, but it did not yield the expected results.

Cummulative Paid =

Var MaxDate = max('Calendar'[Date])

Return

calculate([Amount],userelationship('AR'[Payment date],'Calendar'[Date]),filter(all('Calendar'),'Calendar'[Date]<=MaxDate))