DAX Power Pivot Time Intelligence – 10 Easy Steps

Support the Needs of Business Intelligence Using Custom Calendars and Time Period Functions in DAX.

Created by: Gilly Dow
IT Training Consultant

What Will I Learn?

• Support the needs of Business Intelligence using Time Period Functions in DAX
• Create running-totals with DATESYTD(), DATESQTD() & DATESMTD()
• Learn how to use PREVIOUSYEAR() and related DAX functions including PREVIOUSQUARTER(), PREVIOUSMONTH() & PREVIOUSDAY()
• Customise DAX ready Time Intelligence functions with DATEADD()
• Learn how to create base measures including FIRSTDATE(), LASTDATE(), ENDOFMONTH() & STARTOFYEAR()
• Learn how to use CLOSINGBALANCE() family of DAX functions
• Understand the benefits of FILTER() to super-charge measures in place of CALCULATE()'s "simple" filters
• Create a 4-4-5 Custom Calendar in Excel and relate to the existing Data Model
• Learn how to recreate DAX in-built Time Intelligence functions for custom calendars
• Use DAX Patterns to master navigating the custom calendar
• Learn how to over-ride the incoming filter context with the ALL() function
• Suppress totals and subtotals with HASONEVALUE()
• Create % growth calculations and generate reports to impress

Requirements

• You should be familiar with DAX Power Pivot basics i.e. how to create a data model covered in DAX Power Pivot - 10 Easy Steps for Beginners or DAX Power Pivot - 10 Easy Steps for Intermediates
• The course is run in Excel 2016 - however, Excel 2013 & 2010 users can follow along
• IMPORTANT: You need a version of Excel that is compatible with Power Pivot (Excel 2013/2016/2019 Standalone, Office 365, Enterprise E3/E5, Office Professional 2016, etc.)
• This course is designed for PC users (Power Pivot is currently unavailable with Excel for Mac)

Target audience

• Anyone who wants to learn DAX Time Intelligence functions with standard or custom calendars
• All DAX Power Pivot - 10 Easy Steps for Beginners students who want to take their learning to the next level