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
Performing Date Calculations in DAX with a 4-4-5 Calendar
Will it be easy? Yes and no – the first half of the course – the DAX “time-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!
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!
Learn how to create "running-totals" with DAX Time Intelligence function DATESYTD()
Presentation tips and how rearranging fields can produce new insights for your data
If you require Fiscal year end dates this lecture shows how to utilise the optional filter parameters to get exactly what you need.
Consolidate your learning with these practice exercises...
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.
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!
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()
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!
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.
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.
You have come so far - this section recaps all the new DAX functions covered in this section and helps to reinforce key concepts
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.
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
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.
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.
Test yourself and see how much you learned along the way
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.
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().
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
Watch me as I complete the Practice Exercise 4 measures - keep on track and feel fully prepared in taking on the next Step!
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.
Test your knowledge and feel empowered with all your efforts achieved so far!
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.
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"
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!
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
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.
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.
Test yourself and feel empowered, confident and raring to go on...
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.
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.
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...
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...
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.
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.
Get to know the structure of the CustomCal with some old friends - whet-your-appetite with all the goodness to come.
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.
Test your knowledge and feel invincible as your DAX journey continues!
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!
Recreate the PREVIOUSMONTH() function with your first DAX Pattern!
Check you have all the skills and techniques in the bag as I complete the Practice Exercise 7 tasks
Reinforce all you have learned with this recap of key concepts of DAX patterns
Test your knowledge of the DAX Pattern used in our examples and how with the navigation maths works
Learn how to recreate the DATESYTD() function to produce the Year to Date measure for use in your custom calendar
Learn how to manipulate the navigation maths to create a running-total for a custom year-to-date sales measure
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
Marvel at how far you have come with a recap of step 8 and all you have achieved!
The key to the DAX Pattern working correctly is the navigation maths. Test your knowledge and ensure you've got it nailed
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.