Forum Discussion

Tachyon75's avatar
Tachyon75
Frequent Visitor
8 years ago

Cumulative Total

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 m...
Need Help
SteveCampbell's avatar
SteveCampbell
Memorable Member
8 years ago

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's avatar
Tachyon75
Frequent Visitor
8 years ago

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.