Cummulative Totals filtered by type

[jmcph]

Hi!

I humbly ask for your help on presenting my visuals. I am trying to compute for the  Interest which i calculated simply as Cummulative Collection - Loan Amt. However, the measures i created works on the per Loan # and not on the per month table chart. Here's the screenshot of my chart.

My measures are as follows:

Cummulative Loan Amt =

Calculate(

'Loan Releases'[Loan Amount],

Filter( ALL('Calendar'[Date]) ,'Calendar'[Date] <= Max ('Calendar'[Date] )))

CummulativeCollection =

Calculate(

Collect[Collection],

Filter( ALL('Calendar'), 'Calendar'[Date] <= Max ('Calendar'[Date])))

Total Interest =

IF( [CummulativeCollection] > [Cummulative Loan Amt], [CummulativeCollection] - [Cummulative Loan Amt], 0)

How can i present the result on the per Loan # into per a month chart? Any help is greatly appreciated.

Thank you! 

[jmcph]

Hi, 

I am relatively new to PBI and now im stucked on how to formulate a particular measure. 

 

The Loan # is a my unique identifier, i want to compute for the Interest which is the difference of the Attribute[collection] and Attribute[loan amount] . 

What i want to achieve is that if the cummulative collection for the particular month exceeds the loan amount, the difference will be posted as Interest for that month only.  I have visualized how will it look like but im stucked on how can i execute it in PBI. 

Here is my visualization on how can i achieve this. please 

Thank you very much! Any inputs will help me a lot.

[amitchandak]

@jmcph , assuming the date is incremental only

try measures

Cumm Amount = CALCULATE(SUM(Table[Collection Amount]),filter(allselected(Table),Table[collection date] <=max(Table[collection date]])))

Interest = [Cumm Amount] -[Loan Amount]

[jmcph]

Thank you for answering. 

I actually tried that measure already, it only solved the same year loan amt and same year collection. 

What about if the Loan Amt was in different year (year 2018) than the collection date (cummulative exceeds in 2019)? 

If i remove the year 2018 from the slicer it will show 0 interest (which is correct since the loan amount for 2019 is 0). Is there a work around on this? 

[amitchandak]

@jmcph , Try if this can work

sumx(values(Table[Loan]),IF( [CummulativeCollection] > [Cummulative Loan Amt], [CummulativeCollection] - [Cummulative Loan Amt], 0))

[jmcph]

It works like magic! Thank you very much @amitchandak 

[amitchandak · Accepted solution]

@jmcph , If this worked. Please mark the solution. Thanks for the update.