help to create a report of remaining bank deposit quota

[GrapQR]

Hi all team,

I'm looking for some help to create a report of remaining bank deposit quota, with the following:

Table 1: Transactions - Showing deposit history of Company's Branch 1:

We have transactions start date & end date.

Table 2: Rank & Maximum Deposite Quota - Showing Branch 1's maximum investing quota of each bank. 

As you see, the ranking and maximum quota has changed by time.

Another side, The quota could changed if Branch 1 borrow or lend quota from the others Branch 2, 3. So we have table 3.

Table 3: Borrow & Lending Quota - Showing Branch 1's borrow and lending bank quota:

The result I'm looking for is a matrix visual showing:

- Bank ranking.

- Branch 1's deposit Amount to Bank & Bank Branch.

- Maximum (Original) deposite quota.

- Borrow & lending quota.

- Remaining quota = Maximum (Original) deposite quota + Borrow & lending quota - Deposit Amount.

At the selected date (pickup date)

as you see:

I tried some methods, but those didn't work when bank's ranking and maximum quota changed by time; add Bank branch deposit amouth

@Ashish_Mathur 

I saw you help each others 🙂 Could you please support me for this case 🙂

Regards,

Note: Link download file excel: https://gofile.io/d/f1re52

[v-eqin-msft]

Hi @GrapQR ,

I have trouble downloading your sample data...so I built it by myself.

Please take a look at the following steps:

1. Create a calendar table:

Calendar Table = CALENDAR(MIN('Borrow & Lending Quota'[From (Start Date)]),MAX('Rank & Maximum Deposite Quota'[To (End Date)]))

2. Apply the measure to filter, set as "=1"

flag = 
var _date= CALCULATE(MAX('Rank & Maximum Deposite Quota'[From (Start Date)]),FILTER(ALL('Rank & Maximum Deposite Quota'),'Rank & Maximum Deposite Quota'[From (Start Date)]<=SELECTEDVALUE('Table'[Date].[Date])))

return IF(MAX('Rank & Maximum Deposite Quota'[From (Start Date)])=_date,1,0)

3.Get Borrow & lending quota:

Borrow & lending quota = CALCULATE(MAX('Borrow & Lending Quota'[Borrow & Lending Quota Amount]),FILTER('Borrow & Lending Quota','Borrow & Lending Quota'[Of Bank]=MAX('Rank & Maximum Deposite Quota'[Bank name])))+0

 Currently, the final output is shown below:

And actually , I'm a little confused about:

1. How to get the picked up date "2020 July 7" ? Is it related to which date column in the three table?

2.What's the logic of "Deposite Amount"? Why for the first three branch is 0? Why you exclude "Branch San Diego" from the calculation for JPMorgan Chase?

Please provide me with more details, thanks in advance!

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GrapQR]

Thanks for your support @v-eqin-msft 🙂 

Here is my file:

https://gofile.io/d/Df7zR0
As you well see I gone a different way:

The result:

 But it's not work well. I cannot taking deposit analysis by Bank ranking because of there's no relationship between Transactions Table & Bank ranking 😞
T thought that I should add a column name "Bank Ranking" to the Transactions Table, but I don't know how. Bank Ranking is change by time, it's not fixed value.
Can you work this out ?

May you know @Ashish_Mathur ?