Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Calculated Field for year-to-month figure

  1. #1

    Question Calculated Field for year-to-month figure



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a raw data which will be updated each week by other department and I can only use it but I cannot edit the original. So, I call the Pivot table to connect it as external source.

    The raw data consists every month revenue figure (numeric) from last year Jan to current month.
    e.g. Field:
    Jan2011, Feb2011, Mar2011, ... , Dec2011
    Jan2012, Feb2012, Mar2012, ... , Current_Month2012
    The raw data file already pre-set Jan2012 to Dec2012 fields but no figure until the month is past.

    I need to calculate Sum of Year-to-month figure, so I use Calculated Field.
    e.g. Now is Nov 2012 and thus I have the figure up to Oct.
    Sum of year-to-month (last year) = Jan2011 + Feb2011 + ... + Oct2011
    Sum of year-to-month (this year) = Jan2012 + Feb2012 + ... + Oct2012

    My problem is:
    I need to modified the Calculated Field every month by adding the current month field to the formula.
    Any automation I can do?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    What version of Excel are you using? 2007 or 2010?

    Personally, what I'd do with it is to group the dates by year and by month, add a subtotal to the year, and avoid the calculated fields all together. Then use a filter to exclue the dates after the month that you want. Way easier to set up, and you only need to refresh the filter each time the month changes.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Exclamation

    Quote Originally Posted by Ken Puls View Post
    What version of Excel are you using? 2007 or 2010?

    Personally, what I'd do with it is to group the dates by year and by month, add a subtotal to the year, and avoid the calculated fields all together. Then use a filter to exclue the dates after the month that you want. Way easier to set up, and you only need to refresh the filter each time the month changes.
    excel 2010.
    attached my sample file. since the fileds is arrange in column but not in row, so it seems cannot be group as suggested.
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    The issue you've got is that your data is already coming into the PivotCache as a pivoted layout. Since it's the job of the pivottable to turn the data into the format you're bringing in, it's not going to work very well. If you can't change the data source at all, then the only option I can see for fixing this is to go with a macro to change it.

    You keep telling us you can't change the data source, and that you can't add new columns, but I'd like to know a bit more here.

    What are you connecting to in the other workbook? A table or a PivotTable?

    Are you pulling the data directly into a PivotTable in this workbook, or are you pulling it into a table first, then re-summarizing it with a PivotTable?/
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Also, how to you grab the data, an exported file, a query, ADO, or what?

  6. #6
    Quote Originally Posted by Ken Puls View Post
    The issue you've got is that your data is already coming into the PivotCache as a pivoted layout. Since it's the job of the pivottable to turn the data into the format you're bringing in, it's not going to work very well. If you can't change the data source at all, then the only option I can see for fixing this is to go with a macro to change it.

    You keep telling us you can't change the data source, and that you can't add new columns, but I'd like to know a bit more here.

    What are you connecting to in the other workbook? A table or a PivotTable?

    Are you pulling the data directly into a PivotTable in this workbook, or are you pulling it into a table first, then re-summarizing it with a PivotTable?/
    Ans. I create a PT and direct connect to other excel file table.
    Ans. Yes, I am pulling the data directly into a PT because I want to have latest data every time.

  7. #7
    Quote Originally Posted by Bob Phillips View Post
    Also, how to you grab the data, an exported file, a query, ADO, or what?
    The raw data is shared by my another department. I use PT connect as external data source for analysis only.

  8. #8
    I think the simplest way will be to write to macro that transposes the data, then pivot that new data.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    We're crossing posts and answers here. Based on what you've got in this thread, as well as what's in here...

    Yes, I am pulling the data directly into a PT because I want to have latest data every time.
    Pulling into a table is not mutually exclusive of updates. You could avoid a ton of these issues if you change your approach.

    I'm guessing that you started this process by going to Insert-->PivotTable-->Use an External Data Source, is that correct?

    Try this:
    Go to Data --> Exisiting Connections
    Your PivotTable's connection should be right at the top of the list. Click it and choose Open
    Now, select "Table" and click OK

    At this point, you'll get an official Excel table of the source data. And this table WILL update each time you open the file. You can now build your PivotTable using that table as the source.

    May not look like you've accomplished anything different here, except adding an extra step, but that's not true. You've added versatility as you can add new columns to the table, and the formulas in them will automatically apply each time the data is updated.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Quote Originally Posted by Ken Puls View Post
    We're crossing posts and answers here. Based on what you've got in this thread, as well as what's in here...



    Pulling into a table is not mutually exclusive of updates. You could avoid a ton of these issues if you change your approach.

    I'm guessing that you started this process by going to Insert-->PivotTable-->Use an External Data Source, is that correct?
    YES.

    Thanks for your solution. It's a prefect solution for my case and working fine with me.

    Something minor but want some advice. The Table seems will not auto refresh and I need to click by hand (data>refresh all) even though I restart the workbook. Is there any config I need?

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •