Advanced DAX: Contexts, Time Intelligence and Calculation Groups
Advanced DAX (Context, Time Intelligence) is an advanced-level course of 5 lessons (about 1 h 40 min), with 15 exercises and 15 quiz questions. Prerequisites: DAX Fundamentals. The course needs a Windows virtual machine with Power BI, planned for a later phase of the platform.
- Power BI
- Advanced
- 5 lessons
- 1 h 40 min
- 15 exercises
- Windows virtual machine (phase 2)
The course in brief
This advanced course explains what really happens when a DAX measure is evaluated: row context, filter context and context transition. You then master ALL, ALLEXCEPT, ALLSELECTED and KEEPFILTERS, time intelligence (YTD, prior year, variations), running totals, moving averages, rankings and window functions. You finish with calculation groups and performance diagnosis methods.
What you will be able to do
- Predict the result of a formula by analyzing row context, filter context and context transition
- Choose between ALL, REMOVEFILTERS, ALLEXCEPT, ALLSELECTED and KEEPFILTERS
- Build reliable YTD, prior-year and variance measures, bounded to the available periods
- Compute running totals, moving averages, rankings, top N and position-based comparisons
- Create a time calculation group with dynamic formats
- Diagnose and optimize a slow measure
Syllabus: 5 lessons
- Precisely distinguish row context from filter context
- Explain and use context transition
- Predict the result of a formula in a calculated column or an iterator
- 02
CALCULATE Modifiers: ALL, ALLEXCEPT, ALLSELECTED, KEEPFILTERS
- Choose the right modifier to remove or keep filters
- Compute shares relative to the user's selection
- Understand the evaluation order of CALCULATE's arguments
- 03
Time Intelligence: Year-to-Date Totals and Prior-Year Comparisons
- Compute a year-to-date total (YTD)
- Compare a period to the same period of the previous year
- Compute a variance and avoid misleading comparisons
- 04
Running Totals, Moving Averages, Rankings and Window Functions
- Compute a running total and a moving average
- Rank items with RANKX and isolate a top N with TOPN
- Use OFFSET to compare with the previous item in a visual
- 05
Calculation Groups, Performance and Debugging
- Create a calculation group to apply time variants to all measures
- Diagnose a slow measure with the performance analyzer and the DAX query view
- Apply best practices for performant DAX writing
Final project
Time-Based Management Dashboard for a Retailer
From the 2025-2026 monthly sales dataset (Lyon and Lille), build a model and a management page for the executive team: revenue, year-to-date total, prior-year comparison and variance bounded to the available periods, share of each month in the year and in the selection, 3-month moving average, month ranking and top 3. The time variants must go through a calculation group, and you document the performance of the visuals.
Deliverables
- The .pbix file with the marked Dates table, the measures and the Temps calculation group
- A report page: monthly matrix, revenue and moving average line chart, top 3 card, Year slicer
- An acceptance DAX query (DAX query view) checking at least 6 key values
- A one-page note explaining the behavior of three measures (context, modifiers used)
Assessment criteria
- Exact values: YTD June 2026 1,395, prior year June 2026 235, YTD variance +13.0%, running total March 2026 3,180, 3-month moving average March 2026 216.67, top 3 = 810
- Bounded comparisons: no misleading −44.9% and no running total on future months
- Relevant use of modifiers (ALLSELECTED, ALLEXCEPT, KEEPFILTERS)
- Working calculation group with dynamic format
- Readable measures (variables, comments) and verified performance
Frequently asked questions
What is context transition in DAX?
It is the conversion, by CALCULATE, of the current row context into a filter context. It also happens every time you reference a measure in a calculated column or an iterator.
What is the difference between ALL and ALLSELECTED?
ALL (or REMOVEFILTERS) removes all filters, including those from slicers. ALLSELECTED removes only the filters applied by the visual and keeps the user's selection.
Why are my prior-year calculations wrong for the current year?
Often because the current year is incomplete: you are comparing a few months to a full year. You need to bound the comparison to the last available date.
What are calculation groups for?
They apply the same logic (YTD, prior year, variance) to all measures thanks to SELECTEDMEASURE, which avoids duplicating dozens of measures.
This course, told for your job
Say what you want to be able to do: “Advanced DAX (Context, Time Intelligence)” takes its place in your path, with examples from your field. The concepts stay those of the course.