What does the raw data look like?
Would using min([balance]) work instead?
The raw data is monthly, so for the above it would be something like:
I've tried things like min/max, but I'm only able to get the mix or max balance, and what I really want is the last month's data for my grouping period, either quarter or year.
I've tried using things like an index or lookup, but frankly I'm not very good with those functions yet.
Can you post a packaged workbook with some sample data and examples your desired view at different drill levels? I've got a couple of ideas on how to do to this, but they'd depend on what your data looks like.
The data takes the basic form of an as of date, and values for different measures and dimensions, so something like this:
Date Balance Type 9/30/2013 10000000 A 8/31/2013 9995000 A 7/31/2013 9990000 A 6/30/2013 9985000 A 5/31/2013 9980000 A 4/30/2013 9975000 A 3/31/2013 9970000 A 2/28/2013 9965000 A 1/31/2013 9960000 A 9/30/2013 5000000 B 8/31/2013 4999000 B 7/31/2013 4998000 B 6/30/2013 4997000 B 5/31/2013 4996000 B 4/30/2013 4995000 B 3/31/2013 4994000 B 2/28/2013 4993000 B 1/31/2013 4992000
So the rollups should look like this:
January February March April May June July August September 14,952,000 14,958,000 14,964,000 14,970,000 14,976,000 14,982,000 14,988,000 14,994,000
Q1 Q2 Q3 14,964,000 14,982,000
See the attached. It uses a duplicated date dimension that always keeps the month in the level of detail, and a table calculation that only returns the balance for the last month within the current level of the hierarchy. A copy of the table calc is on the Filters shelf to filter for non-Null values to get rid of the extra marks created by the month on the level of detail.
This worked perfectly, thank you!!!
For anyone else working through this same issue, I had a 2nd part which I was able to figure out myself.
In addition to displaying a balance, which didnt sum over date partitions, I had other variables which did like sales for example. For those calculations I changed them to a running total table calculation using a Sum. Now when I drill along dates I can see balances as of the end of a quarter/year, and total "Sales" for a quarter or year.
Again, thank you for time and solution!