Power Pivot Table - Multi-Year Running Total - How do I get it?

[MikeH2]

OK - I know this is a dumb one - but I've gone through both Bill Jelen's DVD and his books and seem to be missing something. ADD maybe?

Trying to calculate the cumulative share count from 20 years of investment data (using Power Pivot, Transaction Table, Calendar Table, Ticker Table).
I've got the incremental number of shares purchased each quarter (largely via dividend reinvestment).
And I've got the total number of shares purchased each year for each ticker.
What I can't figure out is how to aggregate the total number of shares so that I've got an accurate running count over a 20 year time frame.

Would appreciate help.
Thanks in advance
Mike

 

[SpillerBD]

... Pivot Tables can be complicated (only because of the POWER!) and so dependent on your own formatting and data.
However, have you tried the grouping option.
Could you share your book? (via PM)

 

[MikeH2]

Hi Spiller - thanks for the response.
I was actually trying to use Power Pivot's calculated fields to summarize the share count and couldn't figure out how to use the CALCULATE function.
I found a solution in Rob Collie's book on DAX Formulas for Power Pivot -
[Total Sales Life to Date] = CALCULATE([Total Sales],
DATESBETWEEN(Calendar[Date],
FIRSTDATE(ALL(Calendar[Date])),
LASTDATE(Calendar[Date])
))
Hope this helps someone else with the same problem.
Thank again
Mike

 

[SpillerBD]

I still haven't gone DAX. Because my coworker's barely know PivotTables ! and I can't get to far ahead of them right?
No No No! Don't stop improving and gaining knowledge.

Need to hurry up and get discounted 2nd Edition ordered!
DAX Formulas for PowerPivot - by Rob Collie - PowerPivotPro PowerPivotPro
https://www.indiegogo.com/projects/powerpivotpro-dax-formulas-for-powerpivot-2nd-ed--2#/story