Balance of outstanding loans at month ends

[JNurmi]

Hi,

I struggle with trying to calculate balance of outstanding loans at month ends.

My data is as follows:

Id	Amount	Start	Maturity
Loan 1	1,000.00	15.06.2017	03.08.2017
Loan 2	2,500.00	28.06.2017	18.09.2017
Loan 3	1,800.00	17.07.2017	05.08.2017
Loan 4	500.00	15.08.2017	05.10.2017

I would like to present a bar chart in which there are the relevant month end dates (e.g. from a separate calendar table) on the X axis, but then bar values should be as follows:

End of month	Outstanding balance
30.06.2017	3,500.00
31.07.2017	5,300.00
30.08.2017	3,000.00
30.09.2017	500.00

I have been trying to do this with Measure added with a filter, but I cannot pick up the relevant date to compare against the start and maturity dates of loans in order to calculate total amount of loans per each month end.

Any ideas would be warmly welcomed!

[TomMartens · Accepted solution]

Hey,

I would create a measure that looks like this

Outstanding Balance = 
var eofMonth = CALCULATE(MAXX('yourCalendarTable','yourCalendarTable'[DATE])
return
CALCULATE(
SUMX('yourtable',
IF (AND('yourtable'[Start] <= eofMonth, 'yourtable'[Maturity] >= eofMonth)
,'yourtable'[Amount]
,BLANK()
)
)
)

Hope this will work

Regards

[TomMartens · Accepted solution]

Hey,

I would create a measure that looks like this

Outstanding Balance = 
var eofMonth = CALCULATE(MAXX('yourCalendarTable','yourCalendarTable'[DATE])
return
CALCULATE(
SUMX('yourtable',
IF (AND('yourtable'[Start] <= eofMonth, 'yourtable'[Maturity] >= eofMonth)
,'yourtable'[Amount]
,BLANK()
)
)
)

Hope this will work

Regards

[Eric_Zhang · Accepted solution]

@TomMartens

Your approach shall work, just fix a small syntax issue by adding missing closing parenthesis, no offense. 🙂

Outstanding Balance =
VAR eofMonth =
    CALCULATE ( MAXX ( 'yourCalendarTable', 'yourCalendarTable'[DATE] ) )
RETURN
    CALCULATE (
        SUMX (
            'yourtable',
            IF (
                AND ( 'yourtable'[Start] <= eofMonth, 'yourtable'[Maturity] >= eofMonth ),
                'yourtable'[Amount],
                BLANK ()
            )
        )
    )

[JNurmi]

Thank you very much @Eric_Zhang and @TomMartens! Proposed solution worked perfectly, and saved me a lot of effort!

[TomMartens]

@Eric_Zhangno offense taken! Thanks for watching.