Question

How to Calculate Total Sales for the Last 3 Months Dynamically in DAX?
In Power BI, I need to create a DAX measure that calculates the total sales for the last three months relative to the latest available data, regardless of user slicer selections. What is the best way to construct this measure using functions like DATESINPERIOD or CALCULATE to ensure dynamic date filtering?

Answer 1

To dynamically calculate total sales for the last 3 months, ignoring user slicers, use this DAX measure:

TotalSalesLast3Months =
CALCULATE(
    SUM(Sales[OrderAmount]),
    DATESINPERIOD(
        Sales[OrderDate],
        MAX(Sales[OrderDate]),
        -3,
        MONTH
    )
)

Explanation:

• DATESINPERIOD selects a 3-month window ending at the latest available date in the dataset.
• CALCULATE applies this filtered period to sum the OrderAmount.
• This measure dynamically updates based on new data but ignores slicers unless modified with ALLSELECTED().