EARLIER for row context in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the DAX formula to calculate the difference between the current row's sales and the previous row's sales using EARLIER.

Power BI

Sales Difference = Sales[SalesAmount] - CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[ProductID] = [1]))

Drag options to blanks, or click blank then click option'

ACURRENT(Sales[ProductID])

BSales[ProductID]

CPREVIOUS(Sales[ProductID])

DEARLIER(Sales[ProductID])

Attempts:

3 left

💡 Hint

Common Mistakes

Using Sales[ProductID] directly without EARLIER causes incorrect row context.

Using non-existent functions like PREVIOUS or CURRENT.

The EARLIER function is used to access the value of the current row in a nested row context, which is necessary here to compare the current product's sales with previous rows.

2fill in blank

medium

Complete the DAX formula to count how many sales have a higher amount than the current row using EARLIER.

Power BI

Count Higher Sales = COUNTROWS(FILTER(Sales, Sales[SalesAmount] > [1]))

Drag options to blanks, or click blank then click option'

ASales[SalesAmount]

BMAX(Sales[SalesAmount])

CEARLIER(Sales[SalesAmount])

DMIN(Sales[SalesAmount])

Attempts:

3 left

💡 Hint

Common Mistakes

Using Sales[SalesAmount] directly compares the same row to itself, giving wrong results.

Using aggregate functions like MAX or MIN instead of EARLIER.

EARLIER is needed to refer to the current row's SalesAmount inside the FILTER function to compare with other rows.

3fill in blank

hard

Fix the error in the DAX formula that tries to calculate the rank of sales by product using EARLIER.

Power BI

Sales Rank = COUNTROWS(FILTER(Sales, Sales[ProductID] = EARLIER(Sales[ProductID]) && Sales[SalesAmount] > [1])) + 1

Drag options to blanks, or click blank then click option'

AEARLIER(Sales[SalesAmount])

BSales[SalesAmount]

CMAX(Sales[SalesAmount])

DMIN(Sales[SalesAmount])

Attempts:

3 left

💡 Hint

Common Mistakes

Using Sales[SalesAmount] directly causes incorrect row context.

Using aggregate functions like MAX or MIN instead of EARLIER.

The formula needs EARLIER(Sales[SalesAmount]) to correctly compare each row's SalesAmount with the current row's SalesAmount in the FILTER.

4fill in blank

hard

Fill both blanks to calculate the cumulative sales amount per product using EARLIER.

Power BI

Cumulative Sales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[ProductID] = [1] && Sales[Date] <= [2]))

Drag options to blanks, or click blank then click option'

AEARLIER(Sales[ProductID])

BSales[Date]

CEARLIER(Sales[Date])

DSales[ProductID]

Attempts:

3 left

💡 Hint

Common Mistakes

Using Sales[ProductID] or Sales[Date] directly without EARLIER.

Mixing EARLIER with non-related columns.

EARLIER(Sales[ProductID]) and EARLIER(Sales[Date]) are used to refer to the current row's product and date inside the FILTER to calculate cumulative sales correctly.

5fill in blank

hard

Fill all three blanks to create a calculated column that flags if the current sale is the highest for its product using EARLIER.

Power BI

Is Highest Sale = IF(Sales[SalesAmount] = CALCULATE(MAX(Sales[SalesAmount]), FILTER(Sales, Sales[ProductID] = [1] && Sales[Date] <= [2] && Sales[Date] >= [3])), TRUE(), FALSE())

Drag options to blanks, or click blank then click option'

AEARLIER(Sales[ProductID])

BEARLIER(Sales[Date])

CSales[Date]

DMAX(Sales[Date])

Attempts:

3 left

💡 Hint

Common Mistakes

Using Sales[ProductID] or Sales[Date] directly without EARLIER.

Using MAX(Sales[Date]) incorrectly inside FILTER.

EARLIER(Sales[ProductID]) and EARLIER(Sales[Date]) are used to refer to the current row's product and date inside the FILTER to find the highest sale correctly.

Practice

1. What is the main purpose of the EARLIER function in Power BI DAX?

easy

A. To aggregate values across tables

B. To create a filter on a table

C. To access a value from an earlier row context in a calculated column

D. To sort a column in ascending order

Solution

1. Step 1: Understand the role of EARLIER

   EARLIER is used to refer to a value from an outer row context when inside a nested row context, typically in calculated columns.

2. Step 2: Identify the correct purpose

   It helps compare or calculate values by accessing earlier row values, not for filtering, aggregating, or sorting.

3. Final Answer:

   To access a value from an earlier row context in a calculated column -> Option C

4. Quick Check:

   EARLIER = access earlier row context [OK]

Hint: EARLIER grabs outer row values inside nested calculations [OK]

Common Mistakes:

• Thinking EARLIER works in measures
• Confusing EARLIER with aggregation functions
• Using EARLIER to filter tables

2. Which of the following is the correct syntax to use EARLIER in a calculated column to compare current row's Sales with previous row's Sales in the same table?

easy

A. SalesDifference = CALCULATE(Sales) - EARLIER(Sales)

B. SalesDifference = Sales - EARLIER(Sales, 1)

C. SalesDifference = Sales - EARLIER(Sales, 2)

D. SalesDifference = Sales - EARLIER()

Solution

1. Step 1: Recall EARLIER syntax

   EARLIER does not take a second argument. It only takes a single column reference. The syntax EARLIER(Sales, 1) is invalid.

2. Step 2: Identify correct usage

   To compare current row's Sales with the previous row's Sales, use EARLIER(Sales) without a second argument.

3. Final Answer:

   SalesDifference = CALCULATE(Sales) - EARLIER(Sales) -> Option A

4. Quick Check:

   EARLIER takes only one argument [OK]

Hint: EARLIER(column) accesses outer row context value [OK]

Common Mistakes:

• Using EARLIER with multiple arguments
• Passing invalid arguments to EARLIER
• Assuming EARLIER works in measures

3. Given the following calculated column formula in a Sales table:

Rank = CALCULATE(COUNTROWS(Sales), FILTER(Sales, Sales[SalesAmount] > EARLIER(Sales[SalesAmount]))) + 1

What does this formula calculate for each row?

medium

A. The difference between current SalesAmount and the previous row's SalesAmount

B. The rank of the current row's SalesAmount compared to all other rows, with 1 being highest

C. The sum of SalesAmount for all rows greater than current row's SalesAmount

D. The total count of rows where SalesAmount is less than current row's SalesAmount

Solution

1. Step 1: Understand the FILTER and EARLIER usage

   FILTER creates a table of rows where SalesAmount is greater than the current row's SalesAmount (accessed by EARLIER).

2. Step 2: Calculate COUNTROWS and add 1

   COUNTROWS counts how many rows have a higher SalesAmount, then adding 1 gives the rank (1 means highest SalesAmount).

3. Final Answer:

   The rank of the current row's SalesAmount compared to all other rows, with 1 being highest -> Option B

4. Quick Check:

   EARLIER helps compare current row in ranking [OK]

Hint: Count rows with greater value + 1 = rank [OK]

Common Mistakes:

• Thinking it sums values instead of counting rows
• Confusing rank with difference calculation
• Misunderstanding EARLIER's role in FILTER

4. You wrote this calculated column formula:

PreviousSales = CALCULATE(MAX(Sales[SalesAmount]), FILTER(Sales, Sales[Date] = EARLIER(Sales[Date] - 1)))

But it returns an error. What is the likely cause?

medium

A. EARLIER requires a column reference, not an expression like Sales[Date] - 1

B. EARLIER cannot be used with date arithmetic inside FILTER

C. The FILTER function is missing a table argument

D. MAX cannot be used inside CALCULATE

Solution

1. Step 1: Analyze EARLIER usage

   EARLIER expects a column reference to retrieve the earlier row's value, but here it's used inside an expression (Sales[Date] - 1), which is invalid.

2. Step 2: Understand correct EARLIER usage

   EARLIER(Sales[Date]) is valid, but EARLIER(Sales[Date]) - 1 is an expression outside EARLIER's scope and causes error.

3. Final Answer:

   EARLIER requires a column reference, not an expression like Sales[Date] - 1 -> Option A

4. Quick Check:

   EARLIER needs column, not expressions [OK]

Hint: EARLIER only accepts column names, not calculations [OK]

Common Mistakes:

• Using EARLIER inside expressions incorrectly
• Assuming EARLIER works in measures
• Misplacing FILTER arguments

5. You want to create a calculated column in a Sales table that shows the cumulative sales amount up to the current row's date. Which DAX formula correctly uses EARLIER to achieve this?

hard

A. CumulativeSales = SUMX(FILTER(Sales, Sales[Date] >= EARLIER(Sales[Date])), Sales[SalesAmount])

B. CumulativeSales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Date] < EARLIER(Sales[Date])))

C. CumulativeSales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Date] = EARLIER(Sales[Date])))

D. CumulativeSales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Date] <= EARLIER(Sales[Date])))

Solution

1. Step 1: Understand cumulative calculation logic

   Cumulative sales up to current date means summing all sales where the date is less than or equal to the current row's date.

2. Step 2: Check EARLIER usage in FILTER

   EARLIER(Sales[Date]) correctly refers to the current row's date, and FILTER selects all rows with date <= that date.

3. Step 3: Confirm SUM aggregation

   CALCULATE with SUM aggregates SalesAmount over the filtered rows, giving cumulative total.

4. Final Answer:

   CumulativeSales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Date] <= EARLIER(Sales[Date]))) -> Option D

5. Quick Check:

   EARLIER with <= gives cumulative sum [OK]

Hint: Use FILTER with EARLIER date <= current row for cumulative sum [OK]

Common Mistakes:

• Using < instead of <= excludes current row
• Using >= reverses cumulative logic
• Using EARLIER in measures instead of calculated columns