Average Daily Units Sold Calculation in DAX

Code Analysis

The provided code is a DAX (Data Analysis Expressions) formula commonly used in Power BI or SQL Server Analysis Services for calculating a specific measure from data. This particular measure calculates the average daily units sold over a specified date range.

Code Breakdown

AVG Daily Units Sold = 
    CALCULATE(
        AVERAGEX(
            ALLSELECTED(Dates[Date]),
            [Total Units]
        ),
        DATESBETWEEN(
            Dates[Date],
            [Max Sales Date] - 30,
            [Max Sales Date]
        )
    )

1. Function: CALCULATE

Purpose:

• Modifies the context in which data is evaluated. It allows for the calculation of a measure in a modified filter context.

Structure:

CALCULATE(, )

• <expression>: The main expression or calculation. Here, it is represented by AVERAGEX.
• <filter(s)>: One or more filters that modify the context for <expression>.

2. Function: AVERAGEX

Purpose:

• Evaluates an expression over a table and returns the average of the values.

Structure:

AVERAGEX(, )

<table>: The table to iterate over. Here, ALLSELECTED(Dates[Date]).
<expression>: The expression to evaluate for each row of the table. Here, [Total Units].

3. Function: ALLSELECTED
Purpose:

Returns all the rows in a table regardless of any filters that might have been applied, but with specific respect to the filters applied in the current query context.

Structure:
ALLSELECTED()

<table>: The table to consider. Here, Dates[Date].
4. Expression: [Total Units]
Purpose:

Represents the measure for the total units sold.
5. Function: DATESBETWEEN
Purpose:

Returns a table with a single column of dates within a specified date range.
Structure:
DATESBETWEEN(, , )

<dates_column>: The column that contains dates. Here, Dates[Date].
<start_date>: The start date for the range. Here, [Max Sales Date] - 30, which is 30 days prior to the maximum sales date.
<end_date>: The end date for the range. Here, [Max Sales Date].
Simplified Explanation

ALLSELECTED(Dates[Date]):

Creates a context where all selected dates are considered, ignoring any pre-applied filters but respecting the ones applied in the current context.


AVERAGEX(ALLSELECTED(Dates[Date]), [Total Units]):

Calculates the average of [Total Units] for each date in the selected date context.


DATESBETWEEN(Dates[Date], [Max Sales Date] - 30, [Max Sales Date]):

Defines a date range from 30 days before [Max Sales Date] to [Max Sales Date].


CALCULATE(AVERAGEX(...), DATESBETWEEN(...)):

Computes the average daily units sold within the defined date range by modifying the context using DATESBETWEEN.


Key Concepts

Filter Context: A set of filters applied to data or expressions to get specific results.
Row Context: Used by some DAX functions (like AVERAGEX) to iterate through a row of the table, applying the calculation to each row sequentially.
Date Arithmetic: [Max Sales Date] - 30 calculates a date 30 days before the [Max Sales Date].
Additional Examples
To illustrate similar concepts with different methods:

Using SUMX to calculate the total units over the same period:
SUM Daily Units Sold = 
    CALCULATE(
        SUMX(
            ALLSELECTED(Dates[Date]),
            [Total Units]
        ),
        DATESBETWEEN(
            Dates[Date],
            [Max Sales Date] - 30,
            [Max Sales Date]
        )
    )

Using DATEADD instead of manual date arithmetic:
AVG Daily Units Sold = 
    CALCULATE(
        AVERAGEX(
            ALLSELECTED(Dates[Date]),
            [Total Units]
        ),
        DATESBETWEEN(
            Dates[Date],
            DATEADD([Max Sales Date], -30, DAY),
            [Max Sales Date]
        )
    )
For further learning and in-depth understanding of DAX and its functions, please explore learning resources available on the Enterprise DNA Platform.
 Create your Thread using our flexible tools, share it with friends and colleagues. 
 Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built. 
gesture Create Thread 
Description
This DAX formula calculates the average daily units sold over a specified date range in Power BI or SQL Server Analysis Services, utilizing functions like CALCULATE, AVERAGEX, and DATESBETWEEN to modify the context and perform the calculation.
More Code Explainers
 Apache Flink Code Explainer Apache Pig Code Explainer Azure Data Factory Code Explainer C/C++ Code Explainer CouchDB Code Explainer DAX Code Explainer Excel Code Explainer Firebase Code Explainer Google BigQuery Code Explainer Google Sheets Code Explainer GraphQL Code Explainer Hive Code Explainer Java Code Explainer JavaScript Code Explainer Julia Code Explainer Lua Code Explainer M (Power Query) Code Explainer MATLAB Code Explainer MongoDB Code Explainer Oracle Code Explainer PostgreSQL Code Explainer Power BI Code Explainer Python Code Explainer R Code Explainer Redis Code Explainer Regex Code Explainer Ruby Code Explainer SAS Code Explainer Scala Code Explainer Shell Code Explainer SPSS Code Explainer SQL Code Explainer SQLite Code Explainer Stata Code Explainer Tableau Code Explainer VBA Code Explainer