cancel
Showing results for
Did you mean:
Occasional Visitor

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.

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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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
Community Support Team

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

Announcements

#### 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

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

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)