EARLIER for row context in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to identify for each product the number of previous sales transactions that happened before the current transaction date.

📊 Data: You have a sales table with columns: ProductID, TransactionDate, and SalesAmount. Each row is a sales transaction.

🎯 Deliverable: Create a calculated column that counts how many earlier transactions exist for the same product before the current transaction date.

Sample Data

ProductID	TransactionDate	SalesAmount
101	2024-01-01	100
101	2024-01-05	150
102	2024-01-02	200
101	2024-01-10	120
102	2024-01-03	180
103	2024-01-01	90
103	2024-01-04	110
102	2024-01-05	210

1

Step 1: Open Power BI Desktop and load the sales data table with columns ProductID, TransactionDate, and SalesAmount.

No formula needed for this step.

Expected Result

Sales data table is loaded and visible in the Fields pane.

2

Step 2: Create a new calculated column named 'EarlierTransactionsCount' in the sales table.

EarlierTransactionsCount = COUNTROWS( FILTER( Sales, Sales[ProductID] = EARLIER(Sales[ProductID]) && Sales[TransactionDate] < EARLIER(Sales[TransactionDate]) ) )

Expected Result

A new column is added showing, for each row, how many earlier transactions exist for the same product before the current transaction date.

3

Step 3: Verify the calculated column values by checking a few rows manually.

For example, for ProductID 101 on 2024-01-05, count transactions with ProductID 101 and TransactionDate before 2024-01-05.

Expected Result

The value for that row should be 1, since only the transaction on 2024-01-01 is earlier.

4

Step 4: Create a table visual in the report view to display ProductID, TransactionDate, SalesAmount, and EarlierTransactionsCount.

Add fields: ProductID, TransactionDate, SalesAmount, EarlierTransactionsCount to the table visual.

Expected Result

The table visual shows all transactions with the count of earlier transactions per product.

Final Result

ProductID | TransactionDate | SalesAmount | EarlierTransactionsCount
--------------------------------------------------------------
101       | 2024-01-01      | 100         | 0
101       | 2024-01-05      | 150         | 1
102       | 2024-01-02      | 200         | 0
101       | 2024-01-10      | 120         | 2
102       | 2024-01-03      | 180         | 1
103       | 2024-01-01      | 90          | 0
103       | 2024-01-04      | 110         | 1
102       | 2024-01-05      | 210         | 2

✓For each product, the 'EarlierTransactionsCount' column correctly shows how many sales happened before the current transaction date.

✓This helps understand the sequence of sales and can be used for trend or repeat purchase analysis.

Bonus Challenge

Create a measure that calculates the average number of earlier transactions per product across all transactions.

Show Hint

Use AVERAGEX over the sales table with the 'EarlierTransactionsCount' column.