1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dashboard for MTD and YTD Comparisons in One Pivot Table

Discussion in 'Ask an Excel Question' started by dilawer5, Mar 16, 2017.

  1. dilawer5

    dilawer5 New Member

    Messages:
    14
    Is it possible to show columns in one pivot tables like Target/Achievement/Last year same month ach/%age variance/YTD target/Ytd achievement/LY Same Period Ach/Varience

    Please see uploaded file link..

    https://sabercathost.com/8VY7/Primary_Sales_Data_15_16.xlsx
    1. Please fill formula to get YTD in Raw Source data sheet.

    2. Please guide if i insert a blank sheet for helper table then how to use Offset on Pivot Tables so that my front table vlookup or match index formulas remain valid for a changing position of rows column of back end Pivot table.


    https://sabercathost.com/8VY7/Primary_Sales_Data_15_16.xlsx
    https://sabercathost.com/8VY7/Primary_Sales_Data_15_16.xlsx
  2. bobhc

    bobhc Excel Ninja

    Messages:
    3,322
    You can upload a file to this site without forcing members onto unknown sites with restrictions and advertising, just upload a sheet with the relevant information.
  3. dilawer5

    dilawer5 New Member

    Messages:
    14
    I tried to upload but it gave error that my file is too big. I do not intend to invite anyone to a specific page. Solution is requested. Thanks
  4. bobhc

    bobhc Excel Ninja

    Messages:
    3,322
  5. dilawer5

    dilawer5 New Member

    Messages:
    14
    Here is the sheet Sir, But please also answer if I have set raw data correctly.

    Attached Files:

  6. dilawer5

    dilawer5 New Member

    Messages:
    14
    Here is raw data Sales and Targets are shown in different columns and YTD formula for year 1 and Year 2 is required.

    Attached Files:

  7. r2c2

    r2c2 Member

    Messages:
    67
    @dilawer5 For such calculations I suggest using Power Pivot. While you may be able to come up with a concoction of pivot tables & formulas to get the YTD, MTD, SMLY, Variance values, rolling them up sub groups / groups is hard. If you have Excel 2010 (as you are using Slicers, I think you are on 2010 or above), enable Power Pivot and use it. There are built-in DAX formulas to easily calculate what you need.
    dilawer5 and David Evans like this.
  8. dilawer5

    dilawer5 New Member

    Messages:
    14
    I tried in Power Pivot but could not find out formula for YTD & SamePeriodLastYear...... Can I have formula for the same ? Thanks
  9. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,015
    There's couple of issue with your raw data, for smooth calculation.
    Is raw data brought in automatically via query etc in current format?

    Or are these constructed via calculation etc?

    Are you opposed to changing below to single column for date (and names of couple of other columns)?
    upload_2017-3-17_7-35-35.png
    dilawer5 likes this.
  10. dilawer5

    dilawer5 New Member

    Messages:
    14
    Thanks Chihiro for responding. My original raw data is consist of only four columns and left right data is helping columns which I have created to get more filters in output pivot table. Headers name can be changed no issue with that
  11. dilawer5

    dilawer5 New Member

    Messages:
    14
    I am searching for below solution but in power pivot.

    Attached Files:

  12. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,015
    Which columns are those? If possible, upload with raw data table and formula etc to construct the pivottable data source (along with lookup table if any).
    dilawer5 likes this.
  13. dilawer5

    dilawer5 New Member

    Messages:
    14
    Here is the raw data with must columns. Plz apply dax formula in power pivot to get YTD sales and same period last year sales.

    Attached Files:

  14. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,015
    So year starts from Jul?
  15. dilawer5

    dilawer5 New Member

    Messages:
    14
    Yes Sir, Year Starts from July to Jun
  16. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,015
    See attached.

    Step by Step:
    1. First add calculated column [Date] to raw data (added to Column B).
    Formula =DATE(LEFT(C3,4),RIGHT(C3,2),1)

    2. Convert entire data set to Table

    3. Load the table to PowerQuery (PQ) "Table1"

    4. Add custom column in PQ called "Month"
    Formula =Date.Month([Date])

    5. Create Lookup table in PQ by creating new source->other->Blank Query and name it "MonthLU"

    6. Goto Advanced Editor for Query "MonthLU" and Paste in following "M"
    Code (vb):
    let
        MonthLU = #table(
    type table
        [
            #"Month" = Int64.Type,
            #"MonthIndex" = Int64.Type
        ],
    {
        {7, 1},
        {8, 2},
        {9, 3},
        {10, 4},
        {11, 5},
        {12, 6},
        {1, 7},
        {2, 8},
        {3, 9},
        {4, 10},
        {5, 11},
        {6, 12}
    }
    )
    in
        MonthLU
    7. Back in "Table1" query merge queries. Using "Month" column in each table as related column. Once merged, expand "MonthIndex" only. Making sure to uncheck "Use original..."

    8. Add custom column named "FiscalYear" with following.
    Formula = if [MonthIndex] <=6 then #date(Date.Year([Date]),[MonthIndex],1) else #date(Date.Year([Date])-1,[MonthIndex],1)

    9. Change column type appropriately (date, text, wholenumber etc). See Query settings in PQ editor for details.

    10. Close and load both query as connection only.

    11. Open up PowerPivot Wizard and go to Design tab. Click on "Existing Connections". Find "Query - Table1" and import.

    12. Add YTD measure with following formula.
    YTD:=CALCULATE(SUM([ACTUAL SALE]),FILTER(Query,[FiscalYear]>=DATE(Year(MAX([FiscalYear])),1,1) && [FiscalYear]<=Max([FiscalYear])))

    13. Add PrevYTD measure with following formula.
    PrevYTD:=CALCULATE(SUM([ACTUAL SALE]),FILTER(Query,[FiscalYear]>=DATE(Year(MAX([FiscalYear]))-1,1,1) && [FiscalYear]<=DATE(Year(MAX([FiscalYear]))-1,Month(Max([FiscalYear])),1)))

    14. Close PowerPivot Wizard and create PivotTable using PrevYTD & YTD as value field.
    upload_2017-3-17_12-10-48.png

    See attached.

    Attached Files:

    Thomas Kuriakose and dilawer5 like this.
  17. dilawer5

    dilawer5 New Member

    Messages:
    14
    Thanks Chihiro, you are awesome... finally it get solved.. Now I need same columns for Budget and March16 and March17...will they be develop in same way.... how can i add %age variance column from last year?
  18. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,015
    For Budget. Just replace [ACTUAL SALE] for step 12 & 13 with [BUDGET]

    For % variance...

    Add another Measure.
    Growth:=([YTD]-[PrevYTD])/[PrevYTD]

    Then add it to values field. Set number format to %. And set Pivot Table option to show blank for Errors.
    upload_2017-3-17_14-21-26.png
    Thomas Kuriakose and dilawer5 like this.
  19. dilawer5

    dilawer5 New Member

    Messages:
    14
    Chihiro! you have saved me and 5 others a lot of time and panic. Thanks again
  20. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,015
    You are welcome. I learned PowerQuery & Pivot sometime last year and been in love with it :)
    Khalid NGO and dilawer5 like this.
  21. dilawer5

    dilawer5 New Member

    Messages:
    14
    It is giving following error while creating lookup table in power query...Can u please respond asap...thx
    upload_2017-3-19_19-41-31.png

    Attached Files:

  22. dilawer5

    dilawer5 New Member

    Messages:
    14
    I tried many times formula in step 12 but not able to apply. It gives error on &&.
    Please check formula on attached. Thanks a lot

    Attached Files:

  23. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,015
    In which version of Excel did you create PowerPivot?

    At work, I only have PowerPivot with Excel 2010 and unable to see your data model. Can you take a screen shot of what you are doing at step 12 and what error message you are getting?

    Also I don't see any PowerQuery connections in your sample. Did you follow steps outlined in my posts?

Share This Page