pivot table fiscal year

  • Good day


    I am new to pivot tables. I need to be able to import data for a fiscal year. thats easy right. but i have to compare month for month with previous year. I am unable to group it by



    july 2018 | july 2019 | july 2019 - july 2018 | (july 2019 - july 2018) /july 2018 | aug 2018 | aug 2019


    As you can see int the pivot table it brings data only as


    july 2018 | july 2019 | aug 2018 | aug 2019


    And by adding a calculated field it appears after each column, and not after month rage to do the calulations for each diff month.

  • Yes there is. think of a data lik then in pivot you grou

    28 2018-10-19 2018 10 2018-10 1 1721.25 A&E HYPERWORLD A&E HYPERWORLD
    28 2019-10-29 2019 10 2019-10 1 6300 A&E HYPERWORLD A&E HYPERWORLD
    74 2018-05-23 2018 5 2018-05 1 0 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2018-07-12 2018 7 2018-07 1 36611.9 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2018-08-15 2018 8 2018-08 1 8239.13 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2018-08-17 2018 8 2018-08 1 5447.27 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2018-08-28 2018 8 2018-08 1 1550.19 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2018-08-30 2018 8 2018-08 2 -155.02 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2018-08-30 2018 8 2018-08 2 -605.25 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2018-08-30 2018 8 2018-08 2 -3661.19 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2018-09-17 2018 9 2018-09 1 4071 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2018-10-19 2018 10 2018-10 1 9969.52 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2019-02-21 2019 2 2019-02 1 8054.35 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2019-03-11 2019 3 2019-03 1 1680 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2019-03-11 2019 3 2019-03 1 2862 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE
    74 2019-04-09 2019 4 2019-04 1 1516.76 5472 ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE


    after pivoting it

    Years InvDate Values
    2019
    Jan Feb
    Name Sum of InvTotExclDEx Sum of InvTotExclDEx2 Sum of InvTotExclDEx Sum of InvTotExclDEx2
    A&E HYPERWORLD 0 0 0.00%
    ADEO SA (PTY)LTD T/A LEROY MERLIN GREENSTONE 0 8054.35
    AGRICO (PTY) LTD - LICHTENB 0 0 0.00%
    AGRICULTURAL HARDWARE CC T/A J&A BROKERS 0 0 0.00%
    AGRINET (PTY) LTD 0 5760
    AGRI-TECH DISTRIBUTORS(PTY) 0 70032
    AH MARAIS SEUNS (EDMS) BPK 0 0 0.00%


    i need the pivot to have jan 2019 jan 2020 next to each other then with a calculated field net to them for growth and another for difference. then feb 2019, feb 2010 with calculated fields again

  • Pictures

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.


    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.


    2. Make sure that your desired results are also shown (mock up the results manually).


    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).


    4. Try to avoid using merged cells as they cause lots of problems.


    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  • Here you go. main sheet is where i pull data from mysql. then sheet 1 creates the pivot table from main sheet.

    you will see a grouping by month,year but that is not how the data should work as i need jan2019 and jan2018 nect to each other, then a difference between them and growth, then feb to follow etc

  • A possible solution: Copy your PT and Paste Special onto a new sheet. This is now a range. Highlight the Range of Data and load into Power Query/Get and Transform. Within PQ, you can move entire columns as required and then load back into Excel. Not necessarily a pretty solution, but it will provide you with the desired results.