Name: DAX Power Pivot Time Intelligence - 10 Easy Steps
Rating: 4.6 (380 reviews)

Hier kannst du dein Wissen und Können an Millionen von Teilnehmer:innen in aller Welt weitergeben und damit richtig Geld verdienen!

Weitere Infos

Dein Einkaufswagen ist leer.

Weiter einkaufen

Anmelden

Registrieren

Erstellt vonGilly Dow

Zuletzt aktualisiert 4/2021

Englisch

Das wirst du lernen

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

Kursinhalt

13 Abschnitte • 51 Lektionen • 5 Std. 48 Min. Gesamtdauer

Introduction8: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!

DATESYTD()11:21
Learn how to create "running-totals" with DAX Time Intelligence function DATESYTD()
Presentation Tips for DATESYTD()6:37
Presentation tips and how rearranging fields can produce new insights for your data
Adding a FISCAL Year End Date for Running Totals7:57
If you require Fiscal year end dates this lecture shows how to utilise the optional filter parameters to get exactly what you need.
Solutions Practice Exercise 112:34
Consolidate your learning with these practice exercises...
Recap of Step 11:50
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.
Running Totals

Step 2 Objectives1:59
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!
PREVIOUSYEAR()9:46
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()
DATEADD() & SAMEPERIODLASTYEAR()10:05
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!
Solutions Practice Exercise 211:11
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.
Step 2 Extra - Field Selection Tips11:46
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.
Recap of Step 22:24
You have come so far - this section recaps all the new DAX functions covered in this section and helps to reinforce key concepts
Step 2 Quiz

Step 3 Objectives1:14
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.
FIRSTDATE() LASTDATE() ENDOFMONTH() & STARTOFYEAR()11:12
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
Solutions Practice Exercise 311:47
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.
Recap of Step 32:52
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.
Step 3 Quiz

Step 4 Objectives2:00
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.
DATESBETWEEN()4:37
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().
Running Totals #2 - Total-to-Date10:38
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
Solutions Practice Exercise 46:42
Watch me as I complete the Practice Exercise 4 measures - keep on track and feel fully prepared in taking on the next Step!
Recap of Step 42:54
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.
Step 4 Quiz

Step 5 Objectives2:17
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.
FILTER()12:21
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() Example 114:41
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 26:25
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
Solutions Practice Exercise 57: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.
Recap of Step 53:47
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.
Step 5 Quiz

Custom Calendar Objectives4:52
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.
Setup & Preparation7: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.

Step 6 Objectives1:17
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.
Filter Context, behind-the-scenes and Min & Max refresh6:51
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.
FIRSTDATE(), LASTDATE() and ironing-out the Spikes11:58
Get to know the structure of the CustomCal with some old friends - whet-your-appetite with all the goodness to come.
Recap of Step 63:23
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.
Step 6 Quiz

Step 7 Objectives1:51
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!
Introduction to DAX Patterns11:29
Recreate the PREVIOUSMONTH() function with your first DAX Pattern!
Solutions Practice Exercise 78:15
Check you have all the skills and techniques in the bag as I complete the Practice Exercise 7 tasks
Recap of Step 72:51
Reinforce all you have learned with this recap of key concepts of DAX patterns
Step 7 Quiz

Step 8 Objectives1:54
Learn how to recreate the DATESYTD() function to produce the Year to Date measure for use in your custom calendar
Recreate the DATESYTD() function in the CustomCal14:13
Learn how to manipulate the navigation maths to create a running-total for a custom year-to-date sales measure
Solutions Practice Exercise 84:09
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
Recap of Step 81:59
Marvel at how far you have come with a recap of step 8 and all you have achieved!
Step 8 Quiz

Anforderungen

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)

Beschreibung

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.

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 DAX “time-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!

Für wen eignet sich dieser Kurs:

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

Das wirst du lernen

Zugehörige Themengebiete entdecken

Kursinhalt

Introduction1 Lektionen • 9 Min.

Step 1 - Running Totals5 Lektionen • 40 Min.

Step 2 - Date Intervals6 Lektionen • 47 Min.

Step 3 - First & Last Date Functions4 Lektionen • 27 Min.

Step 4 - Dates Between5 Lektionen • 27 Min.

Step 5 - FILTER()6 Lektionen • 47 Min.

Custom Calendars2 Lektionen • 13 Min.

Step 6 - Filter Context & Behind-the-Scenes4 Lektionen • 23 Min.

Step 7 - DAX Patterns4 Lektionen • 24 Min.

Step 8 - Navigation Maths in the DAX Pattern4 Lektionen • 22 Min.

Anforderungen

Beschreibung

Für wen eignet sich dieser Kurs: