Daily DAX : Day 240 LAST

LAST Function in Visual Calculations

Syntax:

LAST(<value>, <axis>)

Description:

The LAST function retrieves the value from the last row of a specified axis in the visual’s data grid. Visual Calculations are a feature in Power BI that allow calculations directly within a visual (e.g., table or matrix), using a simplified syntax that references the visual’s rows, columns, or groups.

    <value>: The field or expression whose value you want to retrieve from the last row.

    <axis>: The axis (e.g., ROWS, COLUMNS, or GROUPS) defining the scope where the last row is determined. If omitted, it defaults to ROWS.

The "last row" is determined by the sorting and filtering applied to the visual’s axis in the current context.

Key Characteristics:  

    Operates on the visual’s data grid, not the underlying data model.

    Context-aware, respecting the visual’s filters, slicers, and sort order.

    Returns a single value from the last row of the specified axis.

    Part of Power BI’s Visual Calculations, which are designed for quick, visual-specific computations without needing complex DAX measures.

Use Case

The LAST function is useful when you need to reference the value in the last row of a visual for calculations, such as comparisons, running totals, or displaying the final value in a sequence.

Example 1: Displaying the Last Value in a Table

Scenario: You have a table visual showing monthly sales (Sales[Amount]) by Date[Month] (sorted ascending). You want to add a calculation to show the sales amount from the last month in the visual.

Visual Calculation:  

DAX

LastMonthSales = LAST([Amount], ROWS)

    Explanation:  

        [Amount] is the field containing sales values.

        ROWS specifies that the function looks at the rows of the visual (i.e., each month).

        The function returns the Amount from the last row (the last month in the sorted table).

    Result: If the table shows January ($100), February ($150), and March ($200), LastMonthSales returns $200 for every row (or as a single value, depending on how it’s used).

Use in Visual: You can add this as a calculated field in the table visual to display the last month’s sales alongside other months or use it in further calculations.

Example 2: Comparing Values to the Last Row

Scenario: In a matrix visual, you want to calculate the difference between each month’s sales and the last month’s sales.

Visual Calculation:  

DAX

SalesDiffFromLast = [Amount] - LAST([Amount], ROWS)

    Explanation:  

        For each row, [Amount] is the sales value for that month.

        LAST([Amount], ROWS) retrieves the sales value from the last row (last month).

        The calculation subtracts the last month’s sales from the current row’s sales.

    Result: For a table with January ($100), February ($150), and March ($200), the output would be:

        January: $100 - $200 = -$100

        February: $150 - $200 = -$50

        March: $200 - $200 = $0

Example 3: Using with COLUMNS Axis

Scenario: In a matrix visual with years in columns and products in rows, you want the sales from the last year for each product.

Visual Calculation:  

DAX

LastYearSales = LAST([SalesAmount], COLUMNS)

    Explanation:  

        COLUMNS refers to the years in the matrix’s column headers.

        LAST([SalesAmount], COLUMNS) retrieves the sales amount from the last year’s column for each product row.

    Result: If the matrix has 2023 ($500) and 2024 ($600) as columns, LastYearSales returns $600 for each product.

Common Use Cases

    Reference Points: Use LAST to grab the final value in a sequence (e.g., last period’s sales, last day’s stock price) for comparisons.

    Dynamic Calculations: Create visual-specific metrics, like the difference from the last value, without writing complex DAX measures.

    Running Totals or Ratios: Combine LAST with other Visual Calculation functions (e.g., RUNNINGSUM) to compute ratios relative to the last value.

    Highlighting Trends: Display the last value in a visual to emphasize the most recent data point in reports.

Key Notes

    Sort Order Matters: The "last" row depends on the visual’s sort order. If you sort Date[Month] descending, the "last" row is the earliest month.

    Axis Scope: Use ROWS for table-like visuals, COLUMNS for matrix column headers, or GROUPS for grouped visuals (e.g., in clustered charts).

    Limitations:  

        Only works within Visual Calculations, not in traditional DAX measures or calculated columns.

        Requires a visual with an axis (e.g., table, matrix). It won’t work in visuals like cards unless they’re reformatted.

    Context Sensitivity: Respects filters and slicers applied to the visual. If a slicer filters out later dates, LAST reflects the last row in the filtered data.

    Performance: Visual Calculations are optimized for visuals, often faster than equivalent DAX measures for simple operations.

Tips for Use

    Check Sorting: Always verify the visual’s sort order to ensure LAST retrieves the intended row.

    Combine with Other Functions: Use LAST with functions like PREVIOUS or NEXT for relative comparisons.

    Test in Context: Since LAST depends on the visual’s data grid, test the calculation in the actual visual to confirm results.

    Use Descriptive Names: Name calculations clearly (e.g., LastMonthSales) to avoid confusion in complex visuals.