Analyzing Database Using PowerPivot and DAX Formulas (Excel 2013/2016) (8 hrs)

Course Code: OA-ADPD-01

In this Power Pivot course, participants will learn how to Power Pivot to analyse large amounts of data, create dynamic reports with Power View as well as how to use the Power Pivot language called Data Analysis Expressions (DAX).

  • Analyse large volumes of data
  • Create dynamic reports
  • Adopt Power Pivot language

Using Power Pivot

  • Power Pivot: Overview
  • Starting Power Pivot
  • Importing from Access
  • Importing from Excel
  • Importing from a Data Feed
  • Power Pivot window
  • Checking relationships
  • Adding columns
  • A quick Pivot Table
  • A very quick Pivot Chart

Introduction to DAX

  • Understanding calculation in DAX
  • DAX syntax
  • Calculated columns
  • Calculated fields

Common DAX functions

  • Statistical functions
  • Logical functions
  • Information functions
  • Mathematical functions
  • Text functions
  • Date and time functions

Date calculations in DAX

  • Working with a dates table
  • How to build a dates table
  • Working with multiple dates tables
  • Differentiating columns in multiple dates tables
  • Calculating working days
  • Aggregating and comparing over time
  • Year-to-Date, Quarter-to-Date, and Month-to-Date
  • Periods from the prior year
  • Difference over previous year
  • Simplifying browsing with a period table
  • Closing balance over time
  • Semiadditive measures
  • OPENINGBALANCE and CLOSINGBALANCE functions
  • Updating balances by using transactions

Participants will benefit from the hands-on practical sessions during the course.

This course is designed to equip professionals working in Finance, Marketing, Economist, Statistical, Mathematics, Computer Science, IT, Analytics, Marketing Research, or Commodity markets with the essential tools, techniques and skills to answer important business questions.

Prerequisite

Participants must have a basic working knowledge of PivotTables and formulas in Excel. Power Pivot function comes standard only in Microsoft Office 2013 Professional Edition.

Duration : 1 day (8 hrs)
Time : 9:00am – 6:00pm

Course Fee : $400.00
Nett Fee Incl. 7% GST : $428.00

(A course in partnership with COMAT)

Key Benefits
  • Analyse large volumes of data
  • Create dynamic reports
  • Adopt Power Pivot language
Course Contents

Using Power Pivot

  • Power Pivot: Overview
  • Starting Power Pivot
  • Importing from Access
  • Importing from Excel
  • Importing from a Data Feed
  • Power Pivot window
  • Checking relationships
  • Adding columns
  • A quick Pivot Table
  • A very quick Pivot Chart

Introduction to DAX

  • Understanding calculation in DAX
  • DAX syntax
  • Calculated columns
  • Calculated fields

Common DAX functions

  • Statistical functions
  • Logical functions
  • Information functions
  • Mathematical functions
  • Text functions
  • Date and time functions

Date calculations in DAX

  • Working with a dates table
  • How to build a dates table
  • Working with multiple dates tables
  • Differentiating columns in multiple dates tables
  • Calculating working days
  • Aggregating and comparing over time
  • Year-to-Date, Quarter-to-Date, and Month-to-Date
  • Periods from the prior year
  • Difference over previous year
  • Simplifying browsing with a period table
  • Closing balance over time
  • Semiadditive measures
  • OPENINGBALANCE and CLOSINGBALANCE functions
  • Updating balances by using transactions
Learning Methodology

Participants will benefit from the hands-on practical sessions during the course.

Who Should Attend

This course is designed to equip professionals working in Finance, Marketing, Economist, Statistical, Mathematics, Computer Science, IT, Analytics, Marketing Research, or Commodity markets with the essential tools, techniques and skills to answer important business questions.

Prerequisite

Participants must have a basic working knowledge of PivotTables and formulas in Excel. Power Pivot function comes standard only in Microsoft Office 2013 Professional Edition.

Course Details

Duration : 1 day (8 hrs)
Time : 9:00am – 6:00pm

Course Fee : $400.00
Nett Fee Incl. 7% GST : $428.00

(A course in partnership with COMAT)

Course Application

Details Registration
February 3, 2020 - February 3, 2020 (9:00 am - 6:00 pm) Not Available
April 2, 2020 - April 2, 2020 (9:00 am - 6:00 pm) Not Available
June 4, 2020 - June 4, 2020 (9:00 am - 6:00 pm) Register Now
August 13, 2020 - August 13, 2020 (9:00 am - 6:00 pm) Register Now
October 8, 2020 - October 8, 2020 (9:00 am - 6:00 pm) Register Now
December 16, 2020 - December 16, 2020 (9:00 am - 6:00 pm) Register Now