Course Objective: In this course, students will build on the skills learned in PowerPivot Part 1 by using calculations and measures to enhance PowerPivot reporting.

Prerequisites: PowerPivot Part 1 or equivalent knowledge is required prior to enrolling in class, as well as having a strong understanding of PivotTables, PivotCharts, Tables and the use of intermediate to advanced Excel functions.

Contact us to discuss if this class is right for you.

Lesson 1: Build DAX Calculations for PowerPivot

• Create Calculated Columns and Measures
• Understand DAX Formulas and Syntax
• Summarize Data using SUMX, MINX, MAXX and AVERAGEX Functions

Lesson 2: Improve Time Intelligence Measures using DAX

• Work with Time Dependent Data
• Use the BLANK Function
• Apply IF in DAX Measures
• Create Measures using FIRSTDATE, LASTEDATE, ENDOFMONTH, STARTOFYEAR and DATESBETWEEN

Lesson 3: Duplicate Value Issues in Related Table Fields

• Resolve Relationship Issues where a Many to Many is required
• Merge Tables using PowerQuery
• Manage and Refresh Tables created in PowerQuery

Lesson 4: Advanced DAX Functions

• Handle Errors in Dax
• Understand How Drop Zones Affect Measures
• Respect and Ignore the Filters
• Build Measures using ALL, ALLEXCEPT, ALLSELECTED, FILTER AND RANK

Lesson 5: Use Variables in DAX

• Learn Variable Syntax in DAX
• Employ Variables in Measures

Lesson 6: Summarize Data using Time Intelligence

• Calculate an Ongoing Cumulative Total
• Create a Rolling Average
• Create a Rolling Twelve Month Total