• 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...

  • 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

dilawer5

Member
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
 
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.
 
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.
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
 
Here is the sheet Sir, But please also answer if I have set raw data correctly.
 

Attachments

  • Dilawer Excel Question.xlsx
    131.1 KB · Views: 7
Here is raw data Sales and Targets are shown in different columns and YTD formula for year 1 and Year 2 is required.
 

Attachments

  • Raw Data Dilawer.png
    Raw Data Dilawer.png
    173.1 KB · Views: 27
@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 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.
I tried in Power Pivot but could not find out formula for YTD & SamePeriodLastYear...... Can I have formula for the same ? Thanks
 
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
 
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
 
...My original raw data is consist of only four columns...

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).
 
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.
 

Attachments

  • RAW DATA.xlsx
    524.4 KB · Views: 10
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:
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.
 

Attachments

  • RAW DATA.xlsb
    507.1 KB · Views: 33
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?
 
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
 
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.
View attachment 39790
Chihiro! you have saved me and 5 others a lot of time and panic. Thanks again
 
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
 

Attachments

  • upload_2017-3-19_19-41-5.png
    upload_2017-3-19_19-41-5.png
    140.6 KB · Views: 7
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
 

Attachments

  • RAW DATA.xlsx
    995 KB · Views: 10
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?
 
Back
Top