DAX Code Snippet to Calculate Year on Year Sales

DAX (Data Analysis Expressions) is a library of functions and operators, used in Power BI, Analysis Services, and Power Pivot in Excel, which can be combined to build formulas and expressions.

Necessary Imports

TIME intelligence functions like DATESYTD, SAMEPERIODLASTYEAR are in-built in DAX, we do not need any separate imports.

DAX Function

Here is the DAX Function for calculating year on year sales:

YoY Sales = 
CALCULATE(
    SUM(Sales[Sales Amount]), 
    SAMEPERIODLASTYEAR('Date'[Date])
)

Documentation

The function calculates the year on year sales. It uses the CALCULATE function to change the context in which the data is analyzed, SAMEPERIODLASTYEAR function fetches the same period data from last year, and SUM function adds up all relevant sales amounts.

Input Validation

The inputs to the function are the data from the Sales table and the Date column from the Date table. The function implicitly assumes that the sales amount is numeric and the date is of type date.

Considerations

To improve error handling and code robustness, consider validating input types, or handling data-related exceptions gracefully within your code.

Usage Example

Imagine you have these two tables in your Power BI model: a sales table named 'Sales' with a 'Sales Amount' column and a separate 'Date' table with a 'Date' column, and you want to calculate the year on year sales. The above DAX function can be used in such scenarios.

To use this code:

1. Open your Power BI Desktop.
2. Go to the modeling tab, and click on the New measure.
3. Paste the above code.
4. Press enter and the new measure is ready for use.
5. You can now use this measure in your visuals and see the YoY Sales.