Concept Breakdown
Cumulative totals are essential for trend analysis. The EARLIER function is crucial in row context operations, especially within iterators like FILTER, to reference a value from an outer row context. When calculating a cumulative total, EARLIER allows you to compare the current row's date with all previous dates in the table, enabling the aggregation of values up to the current point in time. The FILTER function then narrows down the table to only include rows that meet the cumulative condition.
What You Will Learn
Understand when to use FILTER, EARLIER, VAR in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a intermediate-level task and verify the result with a hidden answer check.
Practice in Power BI
The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.
Download and unzip the provided Power BI file.
Open the 'Oscars.pbix' file in Power BI Desktop.
Locate the 'Cumulative Oscars' calculated column, which is currently blank.
Modify the 'Cumulative Oscars' calculated column using the FILTER and EARLIER functions to correctly calculate the running total of 'Oscars Won' up to each film's 'Release Date'.
Sort the table by 'Release Date' to visually confirm the cumulative total is correct.
Create a second calculated column named 'Cumulative Oscars (VAR)' that achieves the same cumulative total without using EARLIER, instead storing each film's 'Release Date' in a variable.
Save the Power BI file as 'Oscar inflation.pbix'.
Starter DAX
Cumulative Oscars = BLANK()Expected Outcome
The 'Cumulative Oscars' column should display the running total of 'Oscars Won' for each film, based on its 'Release Date'. For example, if 'All Quiet on the Western Front' won 2 Oscars and it's the first entry, it should show 2. Subsequent entries should add their 'Oscars Won' to the previous cumulative total. The 'Cumulative Oscars (VAR)' column should match these values.