cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Tabriz Occasional Visitor
Occasional Visitor

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

 

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

 

 MayMayJuneJune
Accounts TRYUSDTRYUSD
70000005 800  
7000003515.4 15.46 
7000004953.05 53.27 
7000013221.4 21.49 
7000013411.38 11.43 
7000013932.07 32.2 
7000015710.29 10.33 
7000016010.59 10.63 
70000163 21.37 21.46
7000018777.31 77.63 
7000020411.37 11.42 
7000021621.43 21.52 
7000022820.6 20.69 
7000025516 16.07 
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)