DAX Power Pivot Time Intelligence – 10 Easy Steps

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

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

What you’ll 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.

Course Content

  • Introduction –> 1 lecture • 9min.
  • Step 1 – Running Totals –> 5 lectures • 40min.
  • Step 2 – Date Intervals –> 6 lectures • 47min.
  • Step 3 – First & Last Date Functions –> 4 lectures • 27min.
  • Step 4 – Dates Between –> 5 lectures • 27min.
  • Step 5 – FILTER() –> 6 lectures • 47min.
  • Custom Calendars –> 2 lectures • 13min.
  • Step 6 – Filter Context & Behind-the-Scenes –> 4 lectures • 23min.
  • Step 7 – DAX Patterns –> 4 lectures • 24min.
  • Step 8 – Navigation Maths in the DAX Pattern –> 4 lectures • 22min.
  • Step 9 – Suppressing & Fixing Totals –> 6 lectures • 40min.
  • Step 10 – % Growth Measures –> 2 lectures • 15min.
  • Bonus & Conclusion –> 2 lectures • 13min.

DAX Power Pivot Time Intelligence - 10 Easy Steps


  • 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).

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
(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.

Here’s what others had to say about the course:

“So far it’s been easy to follow. The instructor is doing a great job of explaining what she is doing. Looking forward to more advanced topics. UPDATE: I’ve completed the course. It’s a great course and I’m glad I took this one, as well as DAX Power Pivot 10 Easy Steps for Beginners. I’ll be referencing the material often, there’s a lot on information that’s new to me. I really want to dive more into custom calendars and DAX patterns. I’ve already been showing my managers at work how powerful DAX functions can be. Gilly Dow did a fantastic job with both of her courses I took on Udemy. She’s very knowledgeable on Excel and Power Pivot topics and her enthusiasm and encouragement always shows in her lectures. She is very responsive to queries from her students; my questions were answered very quickly. I’ve taken other online courses where you never heard back if you were having problems; that’s definitely not an issue here.”

– John Pertell

“Outstanding. It’s explained clear concise wonderful examples that are broken down and clearly explained fantastic course great teacher and teaching style. Highly recommend”

– Stephen Davanzo

“Highly recommended course! For every single function mentioned in the course there are an example and practice exercise. Great!”

– Elbina Rizakhanova

What You’ll Learn:

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 with my guidance and your growing understanding it will be really, really satisfying – trust me!

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!