Blending known fund balances and changes

I'm working with two data sets related to a number of funds...

Table 1 is a list of transactions related to the fund: (Date|Value|Fund|Description) where deposits and withdrawls are listed.

Table 2 is known balances of the funds, taken at random dates (Date|Fund|BalanceOnDate)

I would like to combine the data sets to calculate gains/losses on the funds that are not masked by deposits and withdrawls.

I've tried using running totals, unions and many other methods but with no success.

Any help appreciated.

Many thanks

Tim

Capture.PNG

Transformation

Accepted answers

jrgo

@tim6467,

Slightly different approach than @jdunkerley79 by using the UNION tool to blend vs. a Join Multiple.

tim6467.yxmd

All comments

jdunkerley79

Try the attached.

- Joins the two together (using a Join Multiple) as an Outer Join and merge into a single table

- Sort into Fund, Date and then use a Multi Row to compute balances when null

- Finally fill in the Revaluation changes

Fund Balances.yxmd

jrgo

@tim6467,

Slightly different approach than @jdunkerley79 by using the UNION tool to blend vs. a Join Multiple.

tim6467.yxmd

jdunkerley79

Union probably a better solution I would expect. But would want to ensure processing of revaluation was done in correct sequence if a Deposit/Withdrawl and revaluation occurred on same day

Can't say which way round would be correct without more business knowledge

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703