Power BI DAX Intermediate: Ranking, Logic, Time Intelligence

In this lesson, we explore how to use compound logical tests within DAX to handle scenarios where multiple conditions must be evaluated at once. These kinds of tests are essential when filtering data based on combinations of rules—such as quantity thresholds or product types.

We begin by demonstrating how to apply compound logic using the traditional AND and OR functions, before moving on to the preferred modern approach using the symbolic equivalents && and ||. These alternatives offer better readability and scalability, especially as your logic becomes more complex.

The example used in this session focuses on a promotional campaign involving two product ranges—Brainster and Yalia. You’ll create a DAX measure that filters sales to include only qualifying orders, based on different quantity thresholds for each product. You’ll also learn why functions like RELATED are necessary when working with columns from related tables.

By the end of the lesson, you’ll be able to:

• Construct compound conditions using both AND/OR and &&/||

• Apply complex filters inside CALCULATE using the FILTER function

• Create readable and maintainable logic for real-world business scenarios

• Use symbolic operators to simplify multi-condition filters

This lesson gives you the confidence to handle multi-condition logic in your measures—an essential skill for building accurate and flexible Power BI reports.

In this lesson, we explore the IN operator in DAX—a cleaner, more concise way to test whether a value exists within a specific list. Rather than writing multiple OR statements, the IN operator allows us to pass a set of target values as a simple, readable list.

You’ll begin by building a measure that isolates revenue from specific UK branches—Leeds, Manchester, and Liverpool—using the IN operator inside a CALCULATE expression. Along the way, you’ll see how IN simplifies your logic and improves maintainability compared to using multiple Boolean conditions.

We’ll also introduce the concept of table constructors, which let you define a list of values directly within the DAX expression. These inline tables power the IN operator and are useful for creating compact and efficient filters.

By the end of this lesson, you’ll be able to:

• Replace long OR statements with a single IN condition

• Use table constructors to define value lists on the fly

• Combine IN with CALCULATE and FILTER to apply scoped logic

• Improve the readability and maintainability of your DAX formulas

This is a practical and elegant tool for writing conditional logic in DAX, particularly helpful when working with filters in data modeling and reporting.

In this lesson, we focus on how to extract meaning from unstructured or free-form text using the CONTAINSSTRING function in DAX. This function is especially useful when dealing with datasets that include open-ended fields—such as customer feedback, comments, or survey responses—where exact matches are not practical.

You’ll learn how to build a dynamic measure that searches for specific keywords within a text column. In our example, we analyse a restaurant feedback dataset to detect terms like “positive,” “negative,” or “slow” within the Comments field. Rather than checking for an exact match, CONTAINSSTRING scans each comment to see if the keyword appears anywhere within it.

We’ll walk through how to:

• Use SELECTEDVALUE to capture a keyword from a slicer or matrix visual

• Apply CONTAINSSTRING within CALCULATE to count matching rows

• Build a responsive keyword tracker that updates based on user selections

You’ll also explore a case-sensitive alternative—CONTAINSSTRINGEXACT—and understand when to use it instead.

Finally, we compare CONTAINSSTRING with the IN operator, highlighting when each is most appropriate. While IN is ideal for structured fields like categories or codes, CONTAINSSTRING is your go-to for finding meaning in text-heavy data sources.

By the end of this session, you’ll be able to:

• Create keyword-sensitive measures for free-text fields

• Build interactive visuals that track sentiment or topic frequency

• Extend your data modeling techniques to cover semi-structured datasets

This lesson is especially relevant for reports that analyse user feedback, open-ended survey answers, or support logs—any scenario where extracting insight from words matters.

In this lesson, we take a closer look at how to use the SWITCH function in a more flexible and powerful way by combining it with the TRUE() function. While SWITCH on its own is ideal for equality checks, it becomes limited when you need to test ranges or write more complex conditions. That’s where SWITCH(TRUE()) comes in—allowing you to replace long IF chains with cleaner, more readable DAX logic.

We’ll walk through a real-world scenario involving IT incident data, where each issue has a severity score between 0 and 100. You’ll learn how to use SWITCH(TRUE()) to categorise each incident into a severity level—Low, Moderate, High, or Critical—based on score ranges. The logic is written inside a calculated column, making it ideal for visuals like pie charts or stacked bar graphs.

You’ll learn how to:

• Use SWITCH(TRUE()) to evaluate multiple conditions in order

• Categorise continuous numeric data into labelled groups

• Build a calculated column for use as a visual legend

• Improve code readability over traditional nested IFs

We’ll also cover how to apply the result to a visual, pairing the new Severity Type column with a row-counting measure to show the incident distribution across categories.

By the end of this session, you’ll be able to:

• Replace complex IF logic with structured SWITCH(TRUE()) expressions

• Build robust logic for classifying data into dynamic groups

• Enhance Power BI visuals with more meaningful group labels

This pattern is essential for anyone working with scores, thresholds, or tiered logic in Power BI, and it can dramatically improve both the clarity of your code and the interpretability of your reports.

In this lesson, we explore how and when to use nested IF statements to handle complex branching logic in Power BI. While SWITCH(TRUE()) offers a clean and powerful way to manage linear conditions, nested IFs remain the preferred option when one decision leads to further tests — a structure that’s common in real-world business logic.

Using a dataset of IT issues, we introduce two new fields: Is Executive and SLA Breached. Alongside these, we reference the related Issue Types table, which classifies each issue into categories like “Security” and “Network.” Our goal is to create an “Urgency Label” column that applies multi-layered rules to classify each issue based on severity, user type, and SLA compliance.

You’ll learn how to:

• Construct nested IF statements that test one condition and then branch into further logic based on the result

• Use the RELATED function to incorporate values from a related table

• Build a calculated column suitable for use in visuals like pie charts and treemaps

• Choose between IF and SWITCH based on the shape and complexity of your logic

This session demonstrates that while SWITCH(TRUE()) is great for straightforward rules, IF is more natural when conditions form a decision tree. You'll see how nested IFs provide a clear, structured approach to handling multi-path evaluations that would be clumsy or unreadable with SWITCH.

By the end of this lesson, you’ll be able to:

• Implement branching logic in calculated columns using IF

• Understand when IF is preferable to SWITCH(TRUE())

• Deliver more nuanced labels or classifications in your Power BI reports

This technique is especially useful in operational reporting, exception handling, or customer segmentation, where decisions are rarely linear and require multi-step reasoning.

This lesson introduces two key DAX functions—ISFILTERED and HASONEVALUE—that allow your Power BI reports to respond intelligently to user selections. These functions are particularly useful when you want to customise visuals or labels based on the number of items selected in a filter or slicer.

Using a simple but practical scenario, we demonstrate how to create a dynamic visual title that adapts depending on whether the user has selected one branch, multiple branches, or none at all. When only one branch is selected, the title shows the branch name. If more than one is selected, it shows “Multiple branches.” And when no branches are selected, it reverts to displaying the country name.

You’ll learn how to:

• Use ISFILTERED to detect whether a visual or slicer is applying a filter

• Combine it with HASONEVALUE to test if only a single value is selected

• Use SELECTEDVALUE in combination with nested IF logic to control the label output

• Apply a measure as a dynamic title to charts and visuals using Power BI’s formatting pane

This technique helps create more interactive and user-aware reports by providing immediate visual feedback about the current selection context.

By the end of this lesson, you’ll be able to:

• Detect and react to different filter states in your report

• Build dynamic labels and titles that update automatically

• Improve user clarity in dashboards with layered filtering

Whether you're building executive dashboards or self-service reports, this method adds polish and professionalism by making your visuals adapt to the user's actions in real-time.

In this final lesson of the section, we explore a creative Power BI technique for improving user experience by dynamically switching visuals—without using bookmarks or buttons. This method is particularly useful when the same dataset needs to be visualised differently depending on how much data is selected.

You’ll learn how to display a pie chart for smaller selections (like a single country with few branches), and automatically switch to a tree map when the selection becomes too large for the pie chart to remain effective.

The key technique involves:

• Layering visuals directly on top of each other

• Using conditional DAX measures that return either a value or blank based on slicer selections

• Letting Power BI’s built-in behaviour of hiding visuals with blank values do the work

We guide you step-by-step through creating two visuals (a pie chart and a tree map), applying conditional logic using COUNTROWS, ALLSELECTED, and SELECTEDVALUE, and synchronising the appearance to give users a seamless experience.

You’ll also learn how to:

• Create separate DAX measures to independently control visibility of each chart

• Use the Selection Pane and Edit Interactions to avoid conflicts and unexpected behaviour

• Maintain consistent titles and formatting across visuals for a polished look

This approach is ideal for dashboards with limited space, where different users may interact with slicers in unpredictable ways. By the end of this lesson, you’ll know how to design responsive, context-aware visuals that adjust automatically to user selections—without resorting to navigation tricks or complex page structures.

This lesson introduces and revisits the concept of iterator functions in DAX—specialised versions of standard aggregation functions that evaluate an expression row by row across a table, before returning a final result.

We begin by contrasting iterator functions like SUMX with standard aggregators such as SUM. While SUM works when values already exist in a column (e.g., Sales[Quantity]), it cannot handle more complex calculations like revenue, which require multiplication across multiple columns. For this, SUMX is essential.

Using practical examples, you’ll learn to:

• Calculate Revenue using SUMX with an expression combining Quantity, Unit Price, and Discount.

• Avoid the need for extra calculated columns, helping to reduce memory usage and improve performance.

• Leverage relationships with RELATED inside an iterator to pull in values from other tables—such as retrieving Unit Cost from the Purchases table to compute Total Cost.

By the end of this section, you’ll understand how iterators allow for custom row-level logic while maintaining a tidy and efficient data model. This is a critical technique when working with real-world business datasets where values must often be derived on the fly.

n this lesson, we expand our understanding of iterator functions by exploring MAXX and MINX, which are used to evaluate row-by-row expressions and return the maximum or minimum result from a table.

We begin with a scenario focused on customer payment behaviour, where we calculate how quickly and how slowly customers have paid their invoices. Rather than storing intermediate results in calculated columns, we demonstrate how to perform these evaluations dynamically using iterator functions.

You will learn to:

• Use MINX to calculate each customer’s fastest payment duration by evaluating the difference between invoice and payment dates.

• Use MAXX to calculate the slowest payment time using the same logic.

• Apply these measures to customer-level visuals for insight into cash flow reliability and exceptions.

• Extend the technique with AVERAGEX to compute average payment time, providing a balanced view of typical customer behaviour.

These functions are particularly helpful for analysing time-based or conditional metrics without inflating the model with extra columns. While not as commonly used as SUMX, MAXX, MINX, and AVERAGEX are essential tools for DAX developers performing more granular performance or behaviour analysis.

In this lesson, we introduce the CONCATENATEX function, a powerful tool for building dynamic labels in Power BI that respond to multiple selections made by the user. While SELECTEDVALUE works well for single-item slicers, it fails when more than one value is selected. CONCATENATEX solves this by allowing us to construct a readable list—ideal for dynamic titles that reflect current slicer choices.

You will learn how to:

• Combine ALLSELECTED and CONCATENATEX to generate a comma-separated list of selected values (e.g. branches).

• Create a dynamic title that changes depending on how many items are selected—displaying "all branches", a specific branch, or a list of selected branches.

• Use SELECTEDVALUE and COUNTROWS to adapt the label logic for single, partial, or full selections.

• Format and place the resulting title into your report using a shape visual and the “Field value” expression option.

This technique adds clarity and polish to report design, helping users stay aware of their current context. It’s especially effective in reports where slicers influence metrics and trends. Used correctly, CONCATENATEX becomes an essential part of a responsive user interface.

By mastering this approach, you're also reinforcing strong data modelling principles—producing models that not only calculate efficiently but also communicate clearly.

In this lesson, we revisit two legacy DAX functions — EARLIER and EARLIEST — which were originally used to access values from an outer row context during calculated column creation or nested iterations. While no longer recommended for new development, these functions still appear in older Power BI models and remain important for understanding and maintaining legacy solutions.

You’ll learn how these functions operate in a multi-level row context and why they were once essential for building expressions that needed to reference values outside the current loop. Using a worked example from a customer service dataset, we’ll show how EARLIER enables comparisons between the current row and other rows with the same staff member and date — such as calculating what percentage of the daily call time each call represents.

The lesson walks through:

• How row context is created during calculated column logic

• How nested FILTER or iterator functions introduce additional row contexts

• How EARLIER retrieves values from a previous iteration level

• When and why EARLIEST might be used instead of specifying a level number

This knowledge is especially valuable when working on inherited reports or long-standing data modeling projects, where understanding older DAX approaches remains essential.

In this lesson, we show how to modernise older DAX patterns by replacing the now-outdated EARLIER and EARLIEST functions with variables. These legacy functions were once widely used to manage row context in calculated columns, especially when nested iterations were involved. However, the introduction of variables (using the VAR keyword) has made these functions largely unnecessary.

We begin by revisiting a classic use case: calculating the percentage of time a single call represents within a staff member’s working day. Previously, this would involve multiple calls to EARLIER inside a FILTER expression. We then walk through how to achieve the same result using variables instead—leading to clearer, more maintainable code.

The lesson covers:

• How to extract values from the current row using VAR

• How to use those variables inside CALCULATE and FILTER

• Why variables improve performance and readability over older patterns

• How to break calculations into logical steps using named expressions

By the end of the session, you’ll be able to:

• Refactor existing code that uses EARLIER or EARLIEST

• Apply best practices for using variables in row-level calculations

• Improve the clarity and maintainability of your data modeling logic in Power BI

This approach is now considered a standard part of writing professional DAX and helps ensure your code is robust, scalable, and easier for others to follow.

In this lesson, we explore one of the most important yet often misunderstood concepts in DAX: context transition. When using iterator functions like AVERAGEX or SUMX, you are working in row context—but unless that context is explicitly converted into filter context, relationships between tables won’t behave the way you expect.

Through a clear example involving product sales, you’ll see how an initial measure using AVERAGEX over the Products table fails to filter the related Sales table correctly. This happens because the SUM inside the iterator is unaware of the current row—no context transition has occurred.

You’ll then learn how to fix this by wrapping the aggregation inside a CALCULATE function, which forces context transition and ensures that the correct filter is applied. You’ll also see how using a pre-defined measure achieves the same result, since all measures in DAX are evaluated in filter context automatically.

By the end of this lesson, you’ll be able to:

• Understand the difference between row context and filter context

• Recognise when context transition is required inside an iterator

• Use CALCULATE to trigger context transition manually

• Avoid common mistakes when aggregating across related tables

• Build more accurate and reliable DAX measures in complex data modeling scenarios

This concept is essential for anyone building calculations that involve relationships—without it, your results may be technically valid but logically wrong. This session equips you with the precision needed to handle those situations confidently.

In this lesson, we dive into one of Power BI’s most versatile and widely-used DAX functions: RANKX. Ranking is a cornerstone of data analysis—helping to identify top performers, bottom quartiles, or relative positions within a group. The RANKX function provides dynamic, context-sensitive ways to calculate rank across different categories, with powerful customisation options.

We start by demonstrating the basic use of RANKX to rank staff by revenue. You’ll learn how to avoid common pitfalls—such as every row being ranked 1—by properly adjusting the filter context using ALL().

Next, we move on to dynamic ranking, where ranks update in response to slicer selections. By using ALLSELECTED(), the ranking becomes interactive and reflects only the subset of data currently visible—ideal for user-driven exploration.

We then introduce group-level ranking, showing how to calculate a person’s position within their branch or department using ALLEXCEPT(). This allows rankings to reset within each group while ignoring unrelated filters.

Finally, we show how to apply RANKX to aggregated categories such as countries or branches, helping to compare overall performance at a higher level.

By the end of this lesson, you’ll be able to:

• Build global and filter-aware rankings using RANKX

• Rank individuals within groups using ALLEXCEPT

• Adjust filter context strategically to control ranking behaviour

• Combine RANKX with visual sort orders for clearer storytelling

Whether comparing salespeople, product lines, or regional performance, RANKX is a key tool in delivering comparative insights with clarity and precision.

In this lesson, we take a deeper look at the RANKX function by exploring its optional arguments. While most users rely on the basic syntax to produce simple rankings, understanding the full parameter set unlocks far more powerful and nuanced analysis.

We begin by introducing the ‘value’ argument, which allows you to compare a row’s current value to a different value—such as prior-year performance—enabling cross-period comparisons within the same ranking logic. This is especially valuable for identifying improvement or decline over time.

Next, we examine how rank order can be reversed using the ASC or DESC argument, depending on whether a higher or lower number represents better performance in your analysis.

The lesson then focuses on handling ties with the DENSE and SKIP options. These control whether tied values share the same rank and what rank is assigned to the next item. This distinction is crucial when rankings are used in competitive contexts or filtered summaries.

You’ll walk away knowing how to:

• Compare current values to alternate periods using the 'value' argument

• Control whether higher or lower values are ranked highest

• Decide how ties should be treated in your ranking output

• Apply these options to create more flexible, business-aligned insights

By mastering these optional arguments, you’ll be equipped to write more precise and meaningful rankings that adapt to your specific reporting needs.

This lesson introduces the TOPN function—a powerful DAX table function used to return the top N rows from a table based on a given expression such as revenue, cost, or performance rating.

Unlike the manual Top N visual filter in Power BI, TOPN is dynamic, reusable, and fully integrated with the DAX engine. It enables advanced filtering inside measures and can be used with CALCULATE to build ranked summaries and comparisons.

We begin by comparing the Top N visual filter to the TOPN function. While the visual filter is useful for quick exploration, it lacks the flexibility and depth of TOPN, which can adapt to changes in slicers, work across visuals, and return actual tables for further computation.

You then learn how to calculate Top 10 Revenue by using CALCULATE combined with TOPN and VALUES, enabling us to rank customers and isolate only the highest contributors in each filter context.

Finally, the lesson shows how to calculate Top 10 Revenue as a Percentage of Total Revenue, offering insight into sales concentration. This is achieved by dividing the filtered result by the unfiltered total using the DIVIDE function.

By the end of the lesson, you’ll be able to:

• Use TOPN to filter a table based on dynamic ranking logic

• Combine TOPN with CALCULATE to build ranked measures

• Contrast TOPN with visual filters for clarity and control

• Create percentage contribution measures for ranked entities

This function is especially useful for leaderboard-style reports, Pareto analyses, and focus visuals where decision-makers want to prioritise key contributors in real time.

In this lesson, we explore the FORMAT function in DAX, a versatile tool that allows you to apply custom number and date formatting directly within your measures. Unlike standard formatting applied through the Power BI interface, FORMAT gives you control at the formula level—enabling dynamic, user-driven presentation logic.

We begin by looking at the difference between permanent formatting, managed in the data model or Fields pane, and dynamic formatting, which is controlled through DAX expressions. You’ll see how FORMAT becomes especially useful in two common reporting scenarios:

• When concatenating numbers and dates into titles or narrative text

• When giving users the ability to choose how values are displayed via slicers

You’ll build a set of disconnected tables to capture user-selected format options, both for dates and numbers. These are then used in new measures to display dynamically formatted revenue, cost, and transaction dates within a table visual. The formatting adjusts instantly based on user input, demonstrating how responsive and interactive your reports can become.

Later, you’ll create a dynamic narrative title that updates in real-time based on filtered dates and formatted values—ideal for use in text boxes, callouts, or report headers.

By the end of this lesson, you’ll be able to:

• Apply dynamic formatting using the DAX FORMAT function

• Use disconnected tables to capture user-selected formatting options

• Display dynamically formatted values in table, matrix, or card visuals

• Build clear, custom titles using DAX and conditional formatting

• Understand the trade-offs between numeric and formatted text measures

This technique is especially valuable in client-facing reports where presentation matters, helping to improve clarity, user experience, and perceived report quality.

In this lesson, we introduce a practical method for adding visual impact to your Power BI reports using RAG status colours — red, amber, and green — to represent performance against defined targets. This colour-based feedback system is commonly used in dashboards and KPI reports to make performance results instantly recognisable.

The scenario focuses on branch-level sales tracking, where each branch has a performance target. You’ll learn how to build a logic-driven DAX measure that assigns a colour based on how actual revenue compares to the target. By defining simple performance bands, such as “on or above target,” “slightly below target,” and “well below target,” the measure returns colour codes that Power BI can use to dynamically style your visuals.

You’ll then apply this logic to common visuals like cards, bar charts, or gauges using conditional formatting, helping end users identify strong or weak performers at a glance.

The lesson also covers:

• Best practices for setting thresholds and using colour responsibly

• Using SWITCH(TRUE()) to structure logic clearly

• Applying conditional formatting using field value mode

• Tips for copying and reusing formatting across different visuals

By the end of this session, you’ll be able to:

• Create RAG-style conditional formatting using a custom measure

• Apply the measure across supported visuals in Power BI

• Communicate performance insights more clearly through visual cues

• Adapt RAG logic to fit different use cases, metrics, or organisational standards

This approach is widely applicable for performance reporting, risk management, and operational dashboards — wherever fast interpretation of results is key.

In this lesson, we explore how to enhance Power BI reports with in-line visual indicators using the UNICHAR function. This approach enables you to display symbols such as check marks, crosses, and star ratings directly within table visuals — providing intuitive, at-a-glance feedback without relying on images or custom visuals.

We begin by building a table that evaluates employee attendance using visual icons. You’ll create measures that return check marks or crosses depending on whether someone started and finished their workday on time. This transforms plain time data into instantly recognisable indicators.

Next, we shift to representing client feedback scores with a 10-star rating system. You’ll use UNICHAR to build a dynamic string of filled and unfilled stars based on average ratings, giving users a quick visual cue of performance. To further enhance readability, you’ll add conditional font colours to the stars — using red, orange, or green depending on the score range.

By the end of this lesson, you’ll be able to:

• Use UNICHAR to inject symbols like check marks and stars into table visuals

• Build logic-based measures that return icons based on thresholds or rules

• Create visual ratings that update dynamically with changes in data

• Apply conditional formatting for text colour based on numeric values

This technique is especially effective in operational dashboards, quality monitoring, and performance reviews — helping to convey key information in a way that’s both immediate and visually engaging.

In this lesson, we explore why every serious Power BI report needs a properly configured calendar table. Time is a critical dimension in almost every business report, and Power BI gives you two ways to work with it: Auto Date/Time or a dedicated date table.

We begin by explaining Power BI’s Auto Date/Time feature, which automatically generates hidden date tables for every date column you use in a visual. While convenient, this approach has limitations — especially when it comes to file size, performance, and flexibility. You’ll learn how to disable this default behaviour and why doing so is considered a best practice in professional reports.

We then shift focus to proper calendar tables — reusable, centralised tables that unlock the full power of DAX time intelligence. You'll learn the advantages of using a single date table across your data model and how to build one using DAX.

You’ll write your own date table using CALENDAR, and then enrich it with key attributes like Year, Quarter, Month, and Day. This lays the foundation for all future time-based measures in the course.

Finally, we introduce the difference between automatic time intelligence (using built-in DAX time functions like DATESYTD and SAMEPERIODLASTYEAR) and manual time intelligence, where you craft your own formulas using CALCULATE, DATEADD, and other flexible functions.

By the end of this lesson, you’ll understand:

• Why the Auto Date/Time feature can cause problems in larger models

• How to disable it and create a central date table instead

• How to generate a robust calendar table using DAX

• The difference between automatic and manual time intelligence in DAX

This lesson introduces the SAMEPERIODLASTYEAR function — a DAX time intelligence function designed to support year-over-year (YoY) comparisons. It enables you to dynamically compare current values with the same period in the previous calendar year, regardless of whether you're viewing data by year, quarter, month, or day.

We begin by looking at how SAMEPERIODLASTYEAR works: it takes the current filter context and shifts it exactly one year back. This makes it ideal for comparing values like revenue, support tickets, or headcount from the same period last year. For example, if you're analysing data from March 2024, the function returns the equivalent data from March 2023.

To explore this in practice, you’ll create a YoY percentage change measure using CALCULATE, a variable to store last year’s value, and DIVIDE to return the result as a percentage. The formula allows you to quantify how much a metric has increased or decreased compared to the same time last year.

You’ll then:

• Create a custom hierarchy in your Date table including Year, Quarter, Month, and Day

• Apply the new measure to a column chart using this hierarchy

• Test drill-down and drill-up behaviour to observe how the function adapts at each level

The visual will show dynamic year-on-year percentage changes across multiple levels of granularity. For example:

• At the year level: overall growth or decline compared to the previous year

• At the quarter or month level: seasonal performance differences

• At the day level: detailed daily variation between the current and previous year

By the end of this session, you will be able to:

• Use SAMEPERIODLASTYEAR in combination with CALCULATE

• Build YoY comparison measures that respond to time hierarchy selections

• Create and apply a date hierarchy for flexible, multi-level analysis

• Configure visuals that support drill-down into historical performance trends

In this lesson, we explore the DATEADD function — a highly flexible DAX tool for shifting time periods in Power BI. Unlike functions like SAMEPERIODLASTYEAR, which always move back exactly one calendar year, DATEADD allows you to shift time forward or backward by any number of days, months, quarters, or years. This flexibility makes it suitable for a wide range of custom time comparisons.

We begin by comparing DATEADD with SAMEPERIODLASTYEAR. You’ll learn why DATEADD is better suited to reports that require user-driven comparisons across varying time frames, such as year-over-year or multi-year analysis

Interactive Scenario: Year-to-Year Comparison

In this exercise, you’ll set up a side-by-side comparison between a base year and a user-selected comparison year. To do this:

• A slicer linked to the DateTable[Year] will allow the user to choose a base year.

• A second slicer, linked to a new disconnected Comparison Year table, lets users choose the year they want to compare it to.

You’ll manually create this comparison table using Enter Data, add it to the model, and confirm that it's not related to any other tables. This design allows us to treat the selected year as an input without affecting filter context in the visuals.

? Creating the Revenue Comparison Measure

With the setup complete, you’ll write a custom DAX measure to calculate the percentage difference between the two selected years. The formula:

• Calculates the offset between the two selected years

• Uses DATEADD to shift the filter context based on that offset

• Returns the percentage change in revenue between the base and comparison year

The IF condition ensures the measure returns nothing when both years are the same, avoiding visual clutter and potential confusion.

? Visual Setup and Interaction

You’ll apply the measure to a column chart and observe how the results change depending on the year selections. For example:

• Setting 2024 as the base year and 2019 as the comparison year displays a percentage increase

• Reversing the selection shows a decline in revenue, as expected

• Selecting the same year for both will return a blank, keeping the visual clean

The visual remains intuitive, and formatting the result as a percentage helps users quickly interpret the direction and scale of change.

By the end of this lesson, you’ll be able to:

• Use DATEADD to shift time context in DAX by any interval

• Compare two user-selected years using a disconnected slicer table

• Build measures that support flexible time comparisons with variable offsets

• Handle edge cases such as matching year selections using BLANK()

• Format and present the comparison clearly in visuals such as column charts

This lesson provides a hands-on example of using DATEADD to create a dynamic, user-controlled time comparison tool—a valuable technique for analysts who need more than fixed, single-year lookbacks.

This lesson introduces the PARALLELPERIOD function — a time intelligence tool in DAX that allows you to shift dates by a fixed interval while controlling the level of time used in the comparison. Unlike functions such as DATEADD or SAMEPERIODLASTYEAR, which preserve the current filter context, PARALLELPERIOD overrides it and forces the calculation to occur at a specific time unit: year, quarter, or month.

We begin by exploring how this behaviour affects your analysis. You’ll learn when it’s beneficial to override context granularity — for example, when benchmarking monthly values against an entire previous year — and how this leads to more consistent and meaningful comparisons in certain scenarios.

Through a guided example, you’ll build a percentage-based comparison that evaluates each month’s performance relative to total revenue from the previous year. This use case demonstrates how PARALLELPERIOD can support strategic reporting goals such as revenue pacing, progress tracking, and performance benchmarking.

The session also covers:

• Key differences between PARALLELPERIOD, DATEADD, and SAMEPERIODLASTYEAR

• How to structure visuals to display context-aware comparisons

• Adding reference lines to provide clear benchmarks within charts

By the end of the lesson, you’ll understand when and why to use PARALLELPERIOD, particularly in cases where maintaining a fixed comparison period is more important than mirroring the current visual context. It’s a reliable tool for reports that track progress toward annual or quarterly goals with consistency across time.

This lesson introduces the concept of running totals in Power BI — cumulative values that grow continuously across a time axis without resetting at the start of each year. Unlike Year-to-Date (YTD) calculations, which restart annually, running totals provide an uninterrupted view of growth or accumulation from the very first date in your dataset.

You’ll learn how to build a DAX measure that calculates a running total of revenue over time. The logic ensures that each data point includes all previous values up to and including the current one. This allows you to create visuals that highlight ongoing progress across multiple years.

Once the measure is created, you'll apply it to a line or bar chart to observe the cumulative trend. Then, using Power BI’s Small Multiples feature, you’ll reframe this data to show individual mini-charts per year. This allows for easy year-over-year comparison while retaining the full progression within each period.

You’ll also enhance the layout by adjusting grid structure, axis formatting, and label display to improve readability and maximise space. The result is a clear and scalable visual that reveals how key metrics accumulate over time — ideal for tracking performance, growth, or milestones in a continuous timeline.

By the end of this lesson, you’ll know how to:

• Construct a measure for calculating a continuous running total

• Apply and format this measure in both standard and small multiple charts

• Use layout and formatting options to improve visual clarity across time

This technique is valuable in many reporting scenarios where cumulative progression matters more than isolated period summaries.

In this lesson, we introduce the DATESINPERIOD function — a versatile DAX tool for defining a moving window of dates relative to a chosen point in time. Unlike PARALLELPERIOD, which imposes a fixed interval based on predefined time units, DATESINPERIOD allows you to define a custom range that shifts with your data, making it ideal for rolling totals and moving averages.

We begin by discussing how the function works and why it’s particularly useful for creating calculations that respond dynamically to filter context. You’ll explore use cases such as Moving Annual Totals (MAT), where each result reflects performance over the trailing 12 months, and 3-Month Moving Averages, which smooth monthly trends for easier interpretation.

To support these use cases, you’ll build two separate visuals:

• One that compares monthly revenue with a trailing annual figure to show long-term growth

• Another using a combo chart to overlay actual revenue with a moving average line, helping to highlight trends without being distracted by short-term fluctuations

Throughout the lesson, you'll see how tying the start date to a dynamic value — such as the latest visible date — enables your calculations to adjust automatically as users filter or drill into your report.

By the end of this session, you’ll be able to:

• Define flexible rolling time windows using DATESINPERIOD

• Create dynamic calculations that move with the user’s context

• Compare actual values with smoother trends for clearer insights

• Apply these techniques to both long-term and short-term metrics

This function is an essential tool for building responsive and insightful Power BI reports that help users understand performance across evolving time frames.

In this lesson, we explore FIRSTDATE and LASTDATE, two DAX time intelligence functions used to identify the first and last date within the current filter context. These functions return single date values that are especially useful for start-to-end comparisons — such as tracking change across a selected period.

You’ll begin by understanding how these functions behave in different contexts, such as when a report is filtered to a single month or year. You’ll learn how they return only the boundary date of that period and why they’re typically used within CALCULATE to extract values associated with the beginning and end of a range.

To demonstrate their practical use, the lesson applies these functions to a financial dataset containing daily fund prices. Rather than summing prices over time — which has little analytical value — you’ll calculate the percentage change between the first and last available prices in the selected period. This mirrors how performance is tracked in real-world financial analysis, but the same pattern can be applied in areas like headcount, stock levels, or opening/closing balances.

Once calculated, this start-to-end percentage change is visualised across multiple periods, making it easy to spot positive or negative trends over time. Slicers are used to filter by attributes such as fund type, enabling targeted comparisons within categories.

By the end of this lesson, you’ll know how to:

• Use FIRSTDATE and LASTDATE to return boundary dates within a visual or filter context

• Combine them with CALCULATE to extract meaningful values for comparison

• Build dynamic measures that calculate change from the start to the end of a time period

• Apply these techniques to financial and non-financial datasets alike

This approach is essential when your focus is on how things have changed from beginning to end — not just how much has occurred during the period.

In this lesson, we explore the DATESBETWEEN function — a highly flexible time intelligence function in DAX that allows you to define exact start and end points for a time window. Unlike other DAX functions that rely on fixed units like months or quarters, DATESBETWEEN offers complete control over your date boundaries, making it ideal for scenarios where standard intervals don’t apply.

You'll begin by understanding how this function works and where it fits among other time intelligence functions. We then apply it to a common business scenario: calculating a 12-week moving average. This use case is particularly valuable in organisations that operate on a weekly cycle or where short-term trend analysis is more relevant than monthly reporting.

You’ll build a visual that compares weekly revenue with a 12-week average line using a combo chart. This allows for a clear, side-by-side view of actual performance and trend, helping users quickly spot fluctuations and patterns. Along the way, we discuss how to structure and format the chart for clarity and consistency, and what to expect when data is incomplete in early periods.

By the end of this lesson, you’ll be able to:

• Use DATESBETWEEN to define custom date ranges in DAX

• Create moving average calculations that aren't tied to fixed calendar units

• Apply these techniques to reports where traditional time functions are too restrictive

• Design visuals that reveal both actual values and smoother trends over time

DATESBETWEEN is particularly useful in models that require week-based analysis, custom calendars, or more precise control over time-based calculations.

In this final time intelligence lesson, we explore a group of straightforward but useful DAX functions: PREVIOUSMONTH, NEXTMONTH, and their counterparts for quarters and years. These functions allow you to shift time context by a single unit and are best used when your analysis requires simple, direct comparisons between adjacent periods.

We focus on PREVIOUSMONTH in a practical scenario — measuring the month-on-month change in revenue. You’ll create a DAX measure that compares the current month’s revenue against the previous month’s, producing a value that highlights both increases and declines over time.

To ensure the result is reliable, you’ll also learn how to enhance the measure by filtering out edge cases — such as the first month in the dataset — to avoid misleading values where no previous month exists.

In the process, we also discuss the strengths and limitations of these dedicated time-shifting functions. While PARALLELPERIOD can replicate this logic with more configuration, functions like PREVIOUSMONTH offer improved readability and intent clarity — making them ideal for simpler, purpose-specific calculations.

By the end of this session, you’ll be able to:

• Use PREVIOUSMONTH to compare current period values with prior periods

• Handle missing data gracefully using conditional logic in DAX

• Apply similar logic using NEXTMONTH or switch to other time intervals as needed

• Understand when to use simple functions versus more flexible alternatives like PARALLELPERIOD

This lesson reinforces the idea that clear, well-targeted functions often improve both performance and maintainability, especially when the business question is narrowly defined.