Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |