Cumulative Total

[Tachyon75]

Hi

I'm trying to do some analysis on customer quality based on the % of the charges we billed that were received across the contracts lifetime. Essentially, I'm running a cumulative charges due measure

Amount Due Cumulative Months = CALCULATE( 'Table1'[Charge Amount Due], FILTER(ALLSELECTED('Table1'[Months Into Contract]),ISONORAFTER('Table1'[Months Into Contract], MAX('Table1'[Months Into Contract]), DESC) ))

and then an amount received cumulative measure

Received Cash Cumulative Months = CALCULATE( 'Table1'[Receipted Cash],FILTER( ALLSELECTED('Table1'[Months Into Contract]),ISONORAFTER('Table1'[Months Into Contract], MAX('Table1'[Months Into Contract]), DESC) ))

with the below measure used to calculate the percentage of charges due that have been received.

Collection % Months (Cash) = MIN([Received Cash Cumulative Months]/[Amount Due Cumulative Months],1)

I know there are probably more efficient ways to do the cumulative calculation, I was just playing around with the measure to achieve the desired result, which I'll outline below.

I've included a screenshot below of an excel dump which shows the resulting behaviour of these measures. The top table shows how the measures calculate line-by-line in a multiple contract scenario. This is acting as desired.

The second table is the result that is returned for the same 2 contracts but removing the actual contract no from the table. In this table, where the months into contract is 6, for example, the amount due cumulative months measure sums all of charge amount due values which are less than or equal to 6 for both contract 101 and contract 102. However, my desired result (shown in the 3rd table) is that for month 6 the cumulative measure will only sum amounts for contracts which themselves have reached that point. Essentially, as contract 201 has not yet reached month 6 I dont want it's charges included in the cumulative measure past month 5, and so at month 6 the amount due cumulative months measure should return $2219.59.

 

I'm aware that is a dreadful explanation, I'm happy to put together a dummy Pbix file if that would be useful.

Thanks in advance for your assisatance, this has had me stumped for ages.

[SteveCampbell]

Can you try:

Amount Due Cumulative Months = 
 
VAR Current_Month = MAX(Table2[Months Into Contract])

RETURN

       CALCULATE(sum(Table2[Charge Amount Due]) 
	    ,Table2[Months Into Contract]<=Current_Month
            ,CONTAINSROW(VALUES(Table2[Contract No]),Table2[Contract No])
      )

This should be your first column, and you can just edit the column name for the second and keep your last measure.

[Tachyon75]

Hi

Thanks for the reply

I've implemented that and it works great based on my ask, so thank you very much.

It has made me realised that there is another issue though which may require a different solution.

'Table1', which contains the historical charge and receipt information, only continues to fill while a contract is active (ie still billing). So for example, if contract 301 runs for 3 months and then terminates (stops billing) it will at that point stop adding rows to 'Table1'. I have another table, call it 'Contract Data', which stores the contract level data and is linked to 'Table1' on contract no. Say contract 301 is actually 5 months in (although it terminated in month 3), I want to create another measure which will still include the relevant charges and receipts for this contract up until month 5.

So essentially, the criteria for whether it is included in the cumulative measure is whether or not the underlying contract has been going for that many months, not whether it had a charge/receipt entry at that point.

Hopefully that makes sense, any help would be much appreciated.

Again, thanks for your earlier help it has been very useful already.