topic Dynamic bank balances, rates and dates to calculate daily overnight bank earnings in DAX Commands and Tips

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

MDP0425 — Thu, 26 Oct 2023 21:14:33 GMT

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.

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

wdx223_Daniel — Fri, 27 Oct 2023 06:21:57 GMT

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])))

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

MDP0425 — Fri, 27 Oct 2023 12:14:43 GMT

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

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

MDP0425 — Fri, 27 Oct 2023 13:30:03 GMT

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!

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

wdx223_Daniel — Mon, 30 Oct 2023 00:01:15 GMT

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

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

MDP0425 — Mon, 30 Oct 2023 19:56:35 GMT

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