Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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 () ) ) )
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
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 () ) ) )
Thank you very much @Eric_Zhang and @TomMartens! Proposed solution worked perfectly, and saved me a lot of effort!
@Eric_Zhangno offense taken! Thanks for watching.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |