Dynamic bank balances, rates and dates to calculate daily overnight bank earnings

[MDP0425]

Trying to figure out how to write dax or create calculated column for overnight bank earnings based on the 2 matching criterion of date & bank. I need the date and bank to match and then conduct the calculation. I have my data model set up like below.

[wdx223_Daniel · Accepted solution]

you can also create a date table to link both tables.

then put the date column of date and the bank of bridge table in the row

Measure=SUMX(SUMMARIZE('BankBalanceTable','BridgeTable'[Bank],'DateTable'[Date]),CALCULATE(SUM('BankBalanceTable'[Balance])*MAX('BankRatesTable'[Daily Rate])))

[wdx223_Daniel · Accepted solution]

you can also create a date table to link both tables.

then put the date column of date and the bank of bridge table in the row

Measure=SUMX(SUMMARIZE('BankBalanceTable','BridgeTable'[Bank],'DateTable'[Date]),CALCULATE(SUM('BankBalanceTable'[Balance])*MAX('BankRatesTable'[Daily Rate])))

[MDP0425]

hey daniel - I guess I am not totally following but created the calendar bridge table and the one relationship has to be inactive. Any insight? Thank you!

[wdx223_Daniel · Accepted solution]

please change all the relationship to single direction, from Bank to other two tables

[MDP0425]

Oh wow! this seems to have worked! I need to check the numbers yet, but thank you so much for your insight and expertise!

[MDP0425]

Oh yes, good idea! This calculation will get a bit more complicated as I will have accounts from the same bank that will not be earning the same rate, but this is a great idea and a push in the right direction. Thank you Daniel