DAX Function for Yearly Total Sum

This document explains how to create a DAX measure to calculate the total sum for every year in your dataset, while ensuring that other filters (such as company) are still taken into account.

Function Overview

Purpose

The purpose of this function is to compute the total sales (or another measure) on a yearly basis, ignoring any filters that apply to product and type dimensions.

Parameters

• Sales Amount: The measure to be summed (e.g., Sales[Amount]).
• Date Table: A properly configured date table for year aggregation.

Return Type

• Returns a numeric value representing the total sum for each year.

Exceptions

• Invalid Measure: If the provided measure is not valid or does not exist.

DAX Code

The following DAX code defines the measure.

Total Sales by Year = 
VAR CurrentYear = YEAR(TODAY())  -- Get the current year as a reference
RETURN
    CALCULATE(
        SUM(Sales[Amount]), -- Total Sales Amount
        ALL(Sales[Product], Sales[Type]), -- Remove filters from Product and Type
        VALUES(Date[Year]) -- Maintain Year context
    )

Explanation of the DAX Code

1. VAR CurrentYear: This variable captures the current year, although it is simply illustrative as we are not using it directly in the calculation.

2. CALCULATE: This function changes the context in which a data expression is evaluated. In our case, it computes the sum of Sales[Amount].

3. SUM(Sales[Amount]): This part sums up the total sales amount.

4. ALL(Sales[Product], Sales[Type]): This removes filters applied to both Product and Type dimensions, allowing the calculation to aggregate over all products and types.

5. VALUES(Date[Year]): This maintains the filters from the Date table, specifically the Year dimension, to ensure the results are segmented by year.

Example Usage

Assuming you have a table labeled Sales with a column Amount and a Date table that includes a Year column, you can use the created measure Total Sales by Year in a Power BI report or Excel PivotTable.

1. Add a visual (like a bar chart) to your report.
2. Use Date[Year] on the axis.
3. Drag the Total Sales by Year measure into the Values field.

This setup will display the total sales for each year, unaffected by any product or type filters while still being influenced by other context filters like company.

Conclusion

This DAX measure efficiently computes the total sum for every year, meeting the requirements to ignore specific dimensions while still respecting other filters. Adhering to best coding practices ensures scalability and maintainability. For further learning on DAX and advanced analytics, consider exploring resources from the Enterprise DNA Platform.