DAX Formula for Rolling 3-Month Sales for India

Formula

To calculate sales for the country India over a rolling 3-month period, you can use the following DAX formula:

Rolling3MonthSalesIndia = 
CALCULATE(
    SUM(Sales[SalesAmount]), 
    FILTER(
        ALL(Sales[OrderDate]), 
        Sales[OrderDate] > EDATE(MAX(Sales[OrderDate]), -3) && 
        Sales[OrderDate] <= MAX(Sales[OrderDate])
    ),
    Sales[Country] = "India"
)

Explanation of the Formula

1. CALCULATE Function: This function modifies the context in which the data is evaluated. It allows you to apply filters and change the way your data is aggregated.

2. SUM Function: The SUM(Sales[SalesAmount]) part of the formula sums up the total sales amount from the Sales table.

3. FILTER Function: This function is used to apply a filter to the date context. It iterates over all dates in the Sales[OrderDate] column, allowing us to create a condition based on the date.

4. ALL Function: The ALL(Sales[OrderDate]) ensures that the calculation is based on the entire date range, ignoring any existing filters on the date.

5. EDATE Function: EDATE(MAX(Sales[OrderDate]), -3) retrieves the date that is 3 months prior to the latest date in the Sales[OrderDate] column.

6. Logical Conditions: The condition within the FILTER checks that:

   • The Sales[OrderDate] is greater than the date from 3 months ago.
   • The Sales[OrderDate] is less than or equal to the maximum date in the data, ensuring that only the sales amounts within the rolling window are summed.

7. Filtering by Country: Finally, the condition Sales[Country] = "India" ensures that only sales figures for India are included in the calculation.

Practical Example

Assume you have a Sales table with the following structure:

Calculation

For the period ending on 2023-04-01, the rolling 3-month sales for India would include:

• January Sales: 100 (from Jan 1) + 200 (from Feb 1) + 250 (from Feb 15)
• February Sales: 200 (from Feb 1) + 300 (from March 1) + 350 (from March 15)
• March Sales: 300 (from March 1) + 350 (from March 15) + 400 (from April 1)

Total = 100 + 200 + 250 (January + February) + 200 + 300 + 350 (February + March) + 300 + 350 + 400 (March + April) = 2150

This formula is efficient for analyzing rolling sales figures and is particularly useful for generating insights over time. Consider exploring related resources on Enterprise DNA for further advanced DAX techniques.