Please Help! Finding difference of columns. Finding change in accounts

[Tabriz]

My task is to analyze deposit portfolio. The attached link is the deposit accounts by months. I want to look at the change in every account in each month. I define movement in deposit accounts as follows: 

New deposit accounts: These are accounts that don't exist in the previous months. So, in this case sum of accounts in the previous months in all currencies are empty. 

Closed deposit accounts: These are accounts that don't exist in the next months. So, in this case sum of accounts in the next months in all currencies are empty. 

Withdrawal from deposit accounts: These are accounts that decline in the current month compared to the previous month. (it could be for one or for all of the currencies) 

Addition to deposit accounts: These are accounts that increase in the current month compared to the previous month. (it could be for one or for all of the currencies) 

Addition and withdrawal from deposit accounts: These are accounts that can increase (decrease) for one currency while decrease (increase) for other currency (at the same time) in the current month compared to the previous month.  

I can do this calculation steps in excel but it is time consuming. 

First step for this calculation is finding difference of balance in accounts. Then we need filtering these accounts per our requirements. I'd be happy if someone helps me to sort it out. 

I attached part of my report to the link below. 

Link for the data

https://www.dropbox.com/s/3zp7g6944sun792/deposit_data.xlsx?dl=0

	May	May	June	June
Accounts	TRY	USD	TRY	USD
70000005		800
70000035	15.4		15.46
70000049	53.05		53.27
70000132	21.4		21.49
70000134	11.38		11.43
70000139	32.07		32.2
70000157	10.29		10.33
70000160	10.59		10.63
70000163		21.37		21.46
70000187	77.31		77.63
70000204	11.37		11.42
70000216	21.43		21.52
70000228	20.6		20.69
70000255	16		16.07

[Anonymous · Accepted solution]

Hi @Tabriz,

According to your description, you can refer to below formula:

prerequisite measures:

TRY Change = if(MAX([TRY June])=BLANK(),0,MAX([TRY June]))-if(MAX([TRY May])=BLANK(),0,MAX([TRY May]))
USD Change = if(MAX([USD June])=BLANK(),0,MAX([USD June]))-if(MAX([USD May])=BLANK(),0,MAX([USD May]))

Requirement measures:

New deposit accounts = if(AND(MAX([TRY May])=BLANK(),MAX([USD May])=BLANK()),TRUE(),FALSE())
Closed deposit accounts = if(AND(MAX([TRY June])=BLANK(),MAX([USD June])=BLANK()),TRUE(),FALSE())
Addition to deposit accounts = if([TRY Change]>0||[USD Change]>0,TRUE(),FALSE())
Addition and withdrawal from deposit accounts = if([TRY Change]=0&&[USD Change]=0,FALSE(),if(OR([TRY Change]>=0&&[USD Change]<=0,[TRY Change]<=0&&[USD Change]>=0),TRUE(),FALSE()))

Regards,

Xiaoxin Sheng