Microsoft Excel Office 365 DAX Formulas for Power Pivot Training Course Outline

Audience: This course is designed for those who need to create formulas using the Data Analysis Expressions language (DAX) to analyse Power Pivot data. Many of the functions are similar to Excel’s but it is vital that you understand the evaluation context.

Course Duration: 1 day

Course Availability: Monday – Saturday

Course Types: Group bookings, public courses, 1-2-1 sessions, bespoke tailored courses and office migrations.

Course Location: This Microsoft Excel Office 365 Power Pivot DAX training can be delivered either at our London training venue or at your offices. 

Tailored Course Content: Course content can be customised to meet your specific requirements, with scheduled dates to suit you.

At Course Completion

After completing this course, students will be able to:

  • Write and understand DAX formulas
  • Use DAX Aggregate functions
  • Understand the DAX evaluation context
  • Use DAX Date and Time functions
  • Use DAX Text functions
  • Use DAX Mathematical functions
  • Use DAX Time Intelligence functions

Course Outline

Module 1: Getting started with DAX

  • Overview of creating the data model
  • Where to write DAX
  • Creating Calculated Columns
  • Creating Measures
  • Operators and Operator precedence
  • Data Types

Module 2: DAX Aggregate functions

  • SUM, AVERAGE, MIN, COUNT, COUNTA and MAX
  • SUMX, AVERAGEX, MINX, COUNTX, COUNTAX and MAXX
  • Statistical functions

Module 3: The fundamental DAX functions

  • Understanding Evaluation Contexts and Relationships
  • Filter and Row Context
  • ALL function
  • ALLEXCEPT function
  • CALCULATE function
  • CALCULATETABLE function
  • RELATED function
  • RELATEDTABLE function
  • FILTER function
  • IFERROR function
  • DISTINCT function
  • EARLIER function

Module 4: DAX Date and Time functions

  • DATE function
  • HOUR function
  • MONTH function
  • YEAR function
  • DAY function
  • WEEKDAY function
  • WEEKNUM function

Module 5: DAX Text functions

  • CONCATENATE function
  • LEFT function
  • RIGHT function
  • TRIM function
  • SUBSTITUTE function

Module 6: DAX Mathematical functions

  • INT function
  • MOD function
  • ROUND function
  • ROUNDUP function
  • ROUNDDOWN function

Module 7: DAX Time Intelligence functions

  • Creating comparisons across comparable time periods.
  • CLOSINGBALANCEMONTH function
  • CLOSINGBALANCEQUARTER function
  • CLOSINGBALANCEYEAR function
  • DATEADD function
  • DATESBETWEEN function
  • DATESINPERIOD function
  • DATESMTD function
  • DATESQTD function
  • DATESYTD function
  • ENDOFMONTH function
  • ENDOFQUARTER function
  • ENDOFYEAR function
  • FIRSTDATE function
  • FIRSTNONBLANK function
  • LASTDATE function
  • LASTNONBLANK function
  • NEXTDAY function
  • NEXTMONTH function
  • NEXTQUARTER function
  • NEXTYEAR function
  • OPENINGBALANCEMONTH function
  • OPENINGBALANCEQUARTER function
  • OPENINGBALANCEYEAR function
  • PARALLELPERIOD function
  • PREVIOUSDAY function
  • PREVIOUSMONTH function
  • PREVIOUSQUARTER function
  • PREVIOUSYEAR function
  • SAMEPERIODLASTYEAR function
  • STARTOFMONTH function
  • STARTOFQUARTER function
  • STARTOFYEAR function
  • TOTALMTD function
  • TOTALQTD function
  • TOTALYTD function