Calendar Table Calculated Columns: Month Year Quarter Fiscal Quarter
See how to:
(00:17) Calendar Table in Diagram View with no Calculated Fields
(00:58) Format Date Column
(01:08) Create Calculated Column #01: Month Number using MONTH function.
(01:53) Create Calculated Column #02: Month Name using FORMAT function and Custom Number Format “mmm”. (FORMAT is substitute for the TEXT function back in Excel).
(03:25) In PowerPivot Grouping does not work like it does in Excel. There is no grouping feature.
(04:24) Sort Month Name column by Month Number so month names appear in chronological order in eth ROW area of the PivotTable.
(05:18) What to do if every cell in the PivotTable shows the same number: You might need a Relationship.
(05:54) Create Relationship between Date column in Calendar Table (Primary Key) and Date in fSales Table in Diagram View.
(06:41) PivotTable that shows Unit Sales by Month.
(07:25) Create Calculated Column #03: YEAR function.
(07:47) PivotTable that shows Unit Sales by Month and Year.
(08:09) Create Calculated Column #04: Quarter Number (Jan – Mar =1) using the CEILING function.
(09:38) Create Calculated Column #05: Quarter Test Label that shows Year and Quarter Number using Ampersand (& Join Symbol) and Text in Double Quotes. Use Year First so Criteria in PivotTable will sort correctly. Like “2014 Q 1”
(10:02) By adding Calculated Columns to our Table, we extend the content and add new Fields (Columns) that we can use as Criteria or Filters in our PivotTable ROWS, COLUMNS, FILTER and Slicer areas of our PivotTable.
(11:02) Create Explicit Formula that we can use over & over will carry the Number Formatting to every new PivotTable that we use. We will create it in the “Measure Grid” below the FSales table so that it will always show up as a Field under the FSales Table in the PivotTable Field List. We will use the SUM function to create a Calculated Field names “TotalUnits”.
(12:49) Create PivotTable for Total Units by Quarter.
(13:35) Discussion about “Filter Context”
(16:00) Create Calculated Column #06: Fiscal Quarter Number using the IF function.
(17:56) Create Calculated Column #07: Fiscal Year using IF function.
(18:22) Create Calculated Column #08: Fiscal Quarter using Ampersand (& Join Symbol) and Text in Double Quotes.
(19:00) Create PivotTable for Total Units by Fiscal Quarter
(19:17) Hide Calculated Columns from PivotTable Field List when the columns will never be used as PivotTable Criteria/Filters by using “Hide From Client Tools” in the Diagram View of the Manage Data Model window.
(02:27) Create PivotTable Report From PowerPivotTable data Model from “Create PivotTable” dialog box 9Alt, N, V) using “Use an external data source” dialog button, then “Choose Connections” button, then Tables Tab in the “Existing Connections” dialog box, and then click on “This Workbook data Model”.
(21:00) Create Report for of Total Units by Month and Year with a Product Slicer.
(22:30) Summary of Video