# Excel Power Pivot: Module 2 – (DAX) Functions for powerful data analysis

Version: Microsoft 365
Duration: 1 day (09:00-17:00)
Price incompany training: € 1210 VAT excl. / Terms for incompany training.

## Description

#### Excel’s strong data analysis tool Power Pivot? Get to know it better during our training course ‘Excel Power Pivot – (DAX) Functions’!

You may already have noticed that Power Pivot contains a lot of new functions. Some of these functions are fairly intuitive to use, but for most you may need the help of a Power Pivot expert, using explicit examples to show you the true power of these functions in combination with PivotTables.

## Objectives

#### Are you struggling with the functions in PowerPivot?

Register now, to:

• Acquire an understanding of the different types of functions in PowerPivot
• Know how to implement the functions for your own analysis solutions

## Prerequisites

It is important to have completed the course ‘Excel PowerPivot – Module 1: Introduction’.

## Content

#### Part 1 Repetition of Data model

• Requirements of an efficient model
• Importing data via Power Query
• Optimising data model (relationships, date table)

#### Part 2 Context

• Row Context
• Filter Context

#### Part 3 Basic functions

• Sum, Min, Max, Average, Count, Counta
• Countrows
• Blank
• HasOneValue
• Switch

#### Part 4 Iterator functions

• What are iterator functions?
• Sumx, Averagex
• Filter
• Context transition

#### Part 5 Changing Context

• Calculate, CalculateTable

#### Part 6 Filter functions

• All, AllExcept, AllSelected
• Filter
• Rankx
• TopN
• Values

#### Part 7 Date and Time Intelligence functions

• Datesbetween, DatesInPeriod
• Datesytd/Datesmtd/Datesqtd
• Totalytd/Totalmtd/Totalqtd
• Endofmonth/Endofquarter/Endofyear
• Firstdate/lastdate
• Firstnonblank/Lastnonblank
• Nextday/Nextmonth/Nextquarter/Nextyear
• Previousday/Previousmonth/Previousquarter/Previousyear
• SamePeriodLastYear
• Startofmonth/Startofquarter/Startofyear

#### Part 8 KPI (Key Performance Indicators)

• Creating a KPI in PowerPivot

#### Part 9 Cube Functions (Optional)

• Converting data from PivotTables into functions

#### Part 10 Working with multiple data fields (optional)

• Multiple relationships between a table and the date table
• Multiple date tables