Power BI DAX Function for Sales Comparison

This document outlines a DAX function to compare sales from April 2010 and April 2011. The goal is to create a measure that calculates the difference in sales between these two months.

Function Overview

Function Name

CompareSales2010AprilAnd2011April

Purpose

To compare total sales between April 2010 and April 2011 and return the difference.

Parameters

• None (uses existing column data)

Return Type

• Numeric (difference in sales)

Exceptions

• The function assumes sales data is available for the specified months. If not, a return value of BLANK() will be used.

DAX Code

CompareSales2010AprilAnd2011April = 
VAR Sales2010April = 
    CALCULATE(
        SUM(Sales[Amount]),
        Sales[OrderDate] >= DATE(2010, 4, 1) &&
        Sales[OrderDate] < DATE(2010, 5, 1)
    )
    
VAR Sales2011April = 
    CALCULATE(
        SUM(Sales[Amount]),
        Sales[OrderDate] >= DATE(2011, 4, 1) &&
        Sales[OrderDate] < DATE(2011, 5, 1)
    )

RETURN
    IF(
        ISBLANK(Sales2010April) && ISBLANK(Sales2011April),
        BLANK(),
        Sales2011April - Sales2010April
    )

Code Explanation

1. Variable Declaration:

   • Sales2010April is calculated by summing the Amount from the Sales table where the OrderDate is within April 2010.
   • Sales2011April follows similarly for April 2011.

2. Conditional Return:

   • The measure checks if both sales values are blank. If true, it returns a blank. Otherwise, it calculates the difference between Sales2011April and Sales2010April.

Usage Example

To use this measure in Power BI:

1. Add the DAX code to the model using the "New Measure" option.
2. Insert a card visual to display the result.
3. Drag and drop the CompareSales2010AprilAnd2011April measure into the card visual.

This will present the sales difference, allowing for quick comparisons between the two specified months.

Additional Notes

• Ensure the sales data in the Sales table is formatted correctly, with OrderDate as a date type.
• You may explore more advanced analysis by incorporating filters or additional contexts based on your requirement.

For further enhancement of your Power BI skills, consider exploring the Enterprise DNA Platform, which offers comprehensive courses and resources tailored for data professionals.