This topic contains 3 replies, has 2 voices, and was last updated by 
-
Posts
-
I’ve been working on this for quite a while now and to no avail. I have a table with four columns (actually have more but will limit to two for simplicity sake) which are File Date, Customer ID, Customer Balance, and Maturity Date. I have created date tables and linked to File Date and Maturity Date. I have a Pivot table that has the Month and Year in the Row Field from the Maturity Date date table and a measure which is a sum of Customer Balance in the Values field. I also have a slicer that selects File Date.
What I am attempting to do is have the pivot table show the customer balances by Month-Year maturing but only show the next six months from the File Date. So for example, if the File Date slicer is set to 07/31/15 I would expect to see August – January in the rows column (which is based on the maturity date) with the Customer Balance totals for each month. Once again, this should change based on the slicer selection. Do I need a disconnected date table?
I think I have it. The only way I could do it was to add a calculated column to my data table which gave the number of months between the File Date and Maturity Date. I used this formula =(YEAR([Date2])-YEAR([Date1]))*12+MONTH([Date2])-MONTH([Date1]). Date 2 = Maturity date and Date 1 = File Date. I added a pivot table filter using this new calcuated column and selected values 1 thru 6. I tested the results selecting different File Date values via the slicer and it works. Now just need to figure out how to get the months to show in chronological order. The sort column Newest to Oldest isn’t cutting it.
If anyone, “umhmm….Tom…” can tell me how to solve this with a measure I would be very interested as I know that measures are preferable to calculated columns with regard to performance. Thanks.
Hello sans holo,
I have seen Avi and Rob do some amazing things with disconnected tables. Sometimes when there is a date range, and supporting intermediate records need to be generated to fill that date range, Power Pivot in Excel 2013 offers a special alternative to disconnected tables.
Could you put together some sample data including the measures you have created so far?
Once implemented, how many rows will there be in your table with file date, customer id, balance and maturity date? Knowing the approximate size of such a table will make a difference to the solution.
Good to visit with you again in the forum!
The forum ‘Power Pivot’ is closed to new topics and replies.