EARLIER

How to Use the Power BI DAX function EARLIER

In this article, we’ll explore how to use the EARLIER function in Power BI DAX and provide some examples to help you get started.

Understanding the EARLIER Function

The EARLIER function in Power BI DAX allows you to reference a column value from a previous row context. This means that you can use the value from a previous row in your current calculation, which can be useful for performing calculations such as running totals or comparing data from different rows.

The syntax for the EARLIER function is as follows:


EARLIER(❰expression❱[,❰n❱])


Where `❰expression❱` is the column or measure that you want to reference, and `❰n❱` is an optional parameter that specifies the number of rows to go back.

Using the EARLIER Function in Power BI DAX

To use the EARLIER function in Power BI DAX, you need to first identify the column or measure that you want to reference. You can then use the EARLIER function to reference that value in your calculation.

Here are some examples of how to use the EARLIER function in Power BI DAX:

Example 1: Calculating a Running Total

Suppose you have a table that contains sales data for different products over time. You want to calculate a running total for each product, so that you can see how sales are trending over time.

To do this, you can use the following DAX formula:


Running Total =

CALCULATE(

SUM(Sales[Amount]),

FILTER(

ALL(Sales),

Sales[Product] = EARLIER(Sales[Product]) &&

Sales[Date] ❰= EARLIER(Sales[Date])

)

)


In this formula, we use the CALCULATE function to calculate the sum of sales for each product. We then use the FILTER function to filter the sales data by product and date, so that we only include sales data for the current product and all dates up to and including the current date. Finally, we use the EARLIER function to reference the product and date from the previous row, which allows us to perform the running total calculation.

Example 2: Comparing Data from Different Rows

Suppose you have a table that contains customer sales data, including the customer name, sales amount, and sales date. You want to compare the sales amount for each customer to the previous month's sales amount.

To do this, you can use the following DAX formula:


Sales Comparison =

IF(

MONTH(Sales[Date]) = MONTH(EARLIER(Sales[Date])),

Sales[Amount] – EARLIER(Sales[Amount]),

BLANK()

)


In this formula, we use the IF function to check if the current sales date and the previous sales date are in the same month. If they are, we subtract the current sales amount from the previous sales amount using the EARLIER function. If they are not in the same month, we return a blank value.

The EARLIER function in Power BI DAX can be a powerful tool for performing calculations that require referencing values from previous row contexts. By understanding how to use this function, you can perform complex calculations and gain insights into your data that would otherwise be difficult to obtain.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

15-17 July 2024
16-18 September 2024
11-13 November 2024

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)