User Profile
EmersonSavage
Frequent Visitor
Joined 9 years ago
User Widgets
Contributions
Combination chart with two separate tables, each having separate date field
I have two tables being brought in from MySQL called transaction and valuation. Valuation shows the value of each security holding of each account holder for every month end date going back 5 years. Transaction shows the net contribution for each contribution and withdrawal transaction for those same accounts but with staggered dates; i.e. they can be on any date, not just month end dates. For example: My goal is to create a combination chart where transaction.TradeDate and valuation.EffectiveDate share the x-axis. In the John Smith example, we would see Sep. 30, Oct. 2, Oct. 5 and Oct. 31 on the x-axis. I would like bars of the combination chart to show the cumulative net contributions to the account between the beginning and ending valuation.EffectiveDate. For Sep. 30, I would want the cumulative contributions to be $157,500 (the total beginning market value of the account). For Oct. 2, the amount would be $172,500 ($157,500 + $15,000 contribution Oct. 2). For Oct. 5 the amount would be $164,500 ($172,500 - $8,000 withdrawal Oct. 5). For Oct. 31, the amount would remain $164,500. I feel a PBI measure may be required here add in the total market value at September 30. I would like the line of the combination chart to simply be the total market value of the account at each month end date. This should be more simple: it would simply be $157,500 at Sep. 30 and $175,000 at Oct. 31 (the total market value of securities for John Smith). Any help with this would be greatly appreciated. Please advise if the above is unclear.
