DAX Power Pivot Time Intelligence - 10 Easy Steps
0.0 (0 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
12 students enrolled
Wishlisted Wishlist

Please confirm that you want to add DAX Power Pivot Time Intelligence - 10 Easy Steps to your Wishlist.

Add to Wishlist

DAX Power Pivot Time Intelligence - 10 Easy Steps

Support the Needs of Business Intelligence Using Time Period Functions in DAX
0.0 (0 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
12 students enrolled
Created by Gilly Dow
Last updated 7/2017
English
Current price: $10 Original price: $30 Discount: 67% off
30-Day Money-Back Guarantee
Includes:
  • 6 hours on-demand video
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
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
  • Know 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
View Curriculum
Requirements
  • You should be familar with DAX Power Pivot basics i.e. how to create a data model
  • The course is run in Excel 2016 - however, Excel 2013 & 2010 users can follow along
Description

This course builds upon the skills learned in the first course in this series DAX Power Pivot – 10 Easy Steps for Beginners.

You have already gotten your hands dirty with DAX & Power Pivot and seen how awesome it is. Now you are ready to take your skills to the next level by delving deeper and acquiring a more thorough understanding of how Power Pivot handles Time data.

Data Analysis Expressions (DAX) includes Time Intelligence functions to support the needs of Business Intelligence analysis by enabling you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.

The first part of the course concentrates on DAX time-ready functions ideal for a standard calendar – the kind you hang on your wall. So, if that’s how you roll at work – this is perfect for you.

The second half raises the stakes and you will learn how to manipulate DAX to generate your own custom functions to work with 4-4-5 calendars used in many different industries including retail.

In order to do this, I’ll introduce you to some new DAX functions that will elevate your skills enabling you to build custom functions for those pesky non-standard calendars.

Even if this doesn’t fit your remit – DO IT ANYWAY! Your level of understanding will increase leaps and bounds and your confidence will soarrrrr!!! Plus, it’s GREAT fun and you’ll learn heaps along the way!

What you will learn in this course:

Performing Date Calculations in DAX with a Standard Calendar

  • Building a standard Calendar table
  • Running Totals with: Month-to-Date (MTD), Quarter-to-Date (QTD) and Year-to-Date (YTD)
  • Computing the moving annual total for Fiscal Year Ends
  • Aggregating and Comparing Growth %’s over time
  • Time intelligence with CALCULATE()
  • Computing periods from the Prior Year
  • Closing Balance, First Date, Last Date, End of Month and Start of Year
  • Computing values Between Dates

Performing Date Calculations in DAX with a 4-4-5 Calendar

  • Building a custom Calendar table
  • Working with multiple Calendar tables
  • Manipulating DAX Patterns
  • Custom Time intelligence with CALCULATE()
  • Custom Time intelligence with CALCULATE(), FILTER() and ALL()
  • Aggregating and Comparing Growth %’s over time

Will it be easy? Yes and no – the first half of the course – the DAXtime-ready” functions are intuitive and straightforward.

The custom Time Intelligence functions you create can be challenging but if it were easy where’s the satisfaction in that??

Thanks for taking the time to check out my course. I can't wait to help you take the next step in developing your DAX & Power Pivot skills. I have used Excel 2016 to demonstrate this fantastic tool but you can follow along in Excel 2010 & 2013 - I'll do my best to point out the differences. If you have any questions about the course, feel free to message me here or check out the FREE preview lectures to learn more.

Take the first steps to a new and rewarding future by clicking on the TAKE THIS COURSE button, located on the top right corner of the page.

I look forward to seeing you on the inside!

Who is the 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
Curriculum For This Course
52 Lectures
06:05:45
+
Introduction
1 Lecture 08:39

In this video lecture I outline the course objectives, how the course runs, what files are available and how to connect to the data model. 

Be clear on what is ahead of you and get fired up for all the great new tools and functions you are just about to learn about, use and create!

Preview 08:39
+
Step 1 - Running Totals
5 Lectures 40:19

Learn how to create "running-totals" with DAX Time Intelligence function DATESYTD()

DATESYTD()
11:21

Presentation tips and how rearranging fields can produce new insights for your data

Presentation Tips for DATESYTD()
06:37

If you require Fiscal year end dates this lecture shows how to utilise the optional filter parameters to get exactly what you need.

Preview 07:57

Consolidate your learning with these practice exercises...

Solutions Practice Exercise 1
12:34

This lecture looks back at the ground you've covered in section 1. Recapping key concepts really helps to embed all the new skills learned.

Recap of Step 1
01:50

Running Totals
3 questions
+
Step 2 - Date Intervals
6 Lectures 47:11

Learn what's coming up in Step 2 - objectives are laid out so you're sure what's ahead and why you are doing it!

Step 2 Objectives
01:59

Learn how to compare this year's performance with last year. If more granularity is required, the PREVIOUS family of DAX time intelligence functions includes PREVIOUSQUARTER(), PREVIOUSMONTH() and PREVIOUSDAY()

PREVIOUSYEAR()
09:46

Just like regular Excel, there's usually more than one way to tackle a problem. In this lecture, you will learn how to customise measures to get just what you need - great news when you need to keep track on past performance and future projections!

DATEADD() & SAMEPERIODLASTYEAR()
10:05

So you have put all your knowledge to the test - in this section you can check you're on the right track and all your good work is paying off by watching me tackle the practice exercises.

Solutions Practice Exercise 2
11:11

This section reinforces the need for correct field and slicer choices to ensure your reports display correct and reliable data. This bonus lecture demonstrates some of the pitfalls and the care required to provide the right tools for end-user ease of use.

Step 2 Extra - Field Selection Tips
11:46

You have come so far - this section recaps all the new DAX functions covered in this section and helps to reinforce key concepts

Recap of Step 2
02:24

Step 2 Quiz
5 questions
+
Step 3 - First & Last Date Functions
4 Lectures 27:05

You are building on your understanding as you progress through the Steps. Learn what's coming up in this next section to ensure you're clear what's coming up and what to expect. Clear expectations and key to your understanding and development.

Step 3 Objectives
01:14

First and Last dates will play a pivotal role in more complex measures as your skills develop. This lecture introduces these stand-alone "base logic" functions that don't require the CALCULATE() function and return dates rather than financial data

FIRSTDATE() LASTDATE() ENDOFMONTH() & STARTOFYEAR()
11:12

You have completed the practice exercises that are helping to embed and reinforce your new skills. Watch this lecture as I complete the reports to ensure you're on the right track.

Solutions Practice Exercise 3
11:47

This lecture looks back at the ground you've covered in section 3. Recapping key concepts really helps to embed all the new skills learned.

Recap of Step 3
02:52

Test yourself and see how much you learned along the way

Step 3 Quiz
3 questions
+
Step 4 - Dates Between
5 Lectures 26:51

Keep on top of your learning objectives with this next Step. Understanding what's coming up and why is key to your learning and development.

Step 4 Objectives
02:00

When you need to take control and not be limited to set time periods - maybe you want to display results for the first or last half of the year. That's where DATESBETWEEN() comes in. Next, you'll learn how to cross natural boundaries for running totals. This will bring into play previous DAX functions you have learned - honing your skills with ALL(), FIRSTDATE() and  LASTDATE().

DATESBETWEEN()
04:37

You know how to create a measure that displays the running total of sales for a whole year. You can also create a measure that crosses this “natural” boundary and just keeps going and going to provide a total-to-date figure

Running Totals #2 - Total-to-Date
10:38

Watch me as I complete the Practice Exercise 4 measures - keep on track and feel fully prepared in taking on the next Step!

Solutions Practice Exercise 4
06:42

This lecture looks back at the ground you've covered in section 4. Recapping key concepts really helps to embed all the new skills learned.

Recap of Step 4
02:54

Test your knowledge and feel empowered with all your efforts achieved so far!

Step 4 Quiz
5 questions
+
Step 5 - FILTER()
6 Lectures 46:39

Ensure you are fully prepared for Step 5 with this run-through of objectives. Be clear on what's coming up to stay on track.

Step 5 Objectives
02:17

Recap on the anatomy of the CALCULATE() function and its use of simple or raw filters. Learn how the FILTER() functions operates and how it differs and how it can "imitate" CALCULATE() - with the use of "wrappers"

FILTER()
12:21

Learn and examine how using FILTER() in place of a simple-filter in CALCULATE() can elevate the power of measure results. Combine this with COUNTROWS() and rocket your results to new heights! 

FILTER() Example 1
14:24

You'll go through another example of CALCULATE(), COUNTROWS() and FILTER() with a step-by-step process run-through to embed and consolidate your understanding

FILTER() Example 2
06:25

Watch me as I complete the Practice Exercise 5 measures - you are building your understanding of FILTER() with a table of products to visualise how and exactly what your FILTER() measures return in preparation for time intelligence purposes.

Solutions Practice Exercise 5
07:25

This lecture looks back at the ground you've covered in section 5 and explains why FILTER() can be used in place of simple filters baked into the CALCULATE() function. Recapping key concepts really helps to embed all the new skills learned.

Recap of Step 5
03:47

Test yourself and feel empowered, confident and raring to go on...

Step 5 Quiz
5 questions
+
Custom Calendars
4 Lectures 31:33

Learn what's coming up and what a 4-4-5 custom calendar is. Understand why they are needed and the differences with a standard calendar. Why DAX built-in time intelligence functions won't work with custom calendars and the importance of continuing your studies.

Custom Calendar Objectives
04:52

In this section I introduce the CustomCal - the custom calendar you will use to complete your custom time intelligence DAX functions. You will link the new calendar to the data model and relate it to the existing standard calendar in preparation for the rest of the course.

Setup & Preparation
07:52

Visualise what's going on in the back ground and how this helps when you start creating custom time intelligence functions. It's all about MIN() and MAX() and how integrating these with FILTER() will play an important role in what's to come...

Filter Context and Behind the Scenes
06:51

Create your first pivot with the CustomCal - get a feel for the new fields and how the table is constructed with this gentle intro. Visit some old friends and ease your way in and iron-out any kinks...

PeriodStart & PeriodEnd
11:58
+
Step 6 - Filter Context & Behind-the-Scenes
4 Lectures 23:29

Be clear on what you are about to achieve in this next section. Find out what's coming up and help prepare your mindset for all that's great about working with custom calendars.

Step 6 Objectives
01:17

This lecture will help you to visualise what the DAX engine is doing in the background - an invaluable aid when you're learning new tools, ideas and concepts.

Filter Context, behind-the-scenes and Min & Max refresh
06:51

Get to know the structure of the CustomCal with some old friends - whet-your-appetite with all the goodness to come.

FIRSTDATE(), LASTDATE() and ironing-out the Spikes
11:58

Take this opportunity to ensure your understanding of the relationship with the standard calendar and the custom calendar is clear and recap exactly what you've covered in this intro to custom calendars section.

Recap of Step 6
03:23

Test your knowledge and feel invincible as your DAX journey continues!

Step 6 Quiz
3 questions
+
Step 7 - DAX Patterns
4 Lectures 24:26

Watch what's coming up as I introduce the concepts of DAX patterns and why all your good work is about to pay out dividends!

Step 7 Objectives
01:51

Recreate the PREVIOUSMONTH() function with your first DAX Pattern!

Introduction to DAX Patterns
11:29

Check you have all the skills and techniques in the bag as I complete the Practice Exercise 7 tasks

Solutions Practice Exercise 7
08:15

Reinforce all you have learned with this recap of key concepts of DAX patterns

Recap of Step 7
02:51

Test your knowledge of the DAX Pattern used in our examples and how with the navigation maths works

Step 7 Quiz
5 questions
+
Step 8 - Navigation Maths in the DAX Pattern
4 Lectures 22:15

Learn how to recreate the DATESYTD() function to produce the Year to Date measure for use in your custom calendar

Step 8 Objectives
01:54

Learn how to manipulate the navigation maths to create a running-total for a custom year-to-date sales measure

Recreate the DATESYTD() function in the CustomCal
14:13

Watch as I walk through the solutions to practice exercise 8 to help embed and consolidate your understanding of the logic behind the navigation maths for the running-total measure

Solutions Practice Exercise 8
04:09

Marvel at how far you have come with a recap of step 8 and all you have achieved!

Recap of Step 8
01:59

The key to the DAX Pattern working correctly is the navigation maths. Test your knowledge and ensure you've got it nailed

Step 8 Quiz
3 questions
3 More Sections
About the Instructor
Gilly Dow
4.9 Average rating
15 Reviews
74 Students
3 Courses
IT Training Consultant, founder gdteach.co.uk

Gilly Dow is an MS Office Master, a Certified Microsoft Excel Expert and Training Consultant with 20 years experience working for the US Military, NHS and multiple private sector companies. Gilly have trained thousands of students from IT Security, MS Office, SAP and Clinical Healthcare applications. GD Teach has been founded especially for Udemy to reach out to a wider audience and deliver practical, step-by-step, quality training that makes a difference to you and your job satisfaction.