Question

I need to calculate a running total but reset it at the start of each new quarter—how can I achieve this in DAX?
You need to create a DAX measure that calculates a running total but resets the total at the beginning of each quarter. How can you structure this calculation using functions like CALCULATE, FILTER, or TIME INTELLIGENCE functions to ensure correct results across different time periods?

Answer 1

To calculate a running total that resets at the start of each quarter, you can use CALCULATE and FILTER in DAX while leveraging the DATESQTD function for quarter-based calculations.

DAX Measure for Running Total (Quarterly Reset)

RunningTotal_Quarterly =  
CALCULATE(  
    SUM( 'Sales'[Amount] ),  
    FILTER(  
        ALLSELECTED( 'Sales' ),  
        'Sales'[Date] >= STARTOFQUARTER( 'Sales'[Date] ) &&  
        'Sales'[Date] <= MAX( 'Sales'[Date] )  
    )  
)

Explanation

1. ALLSELECTED('Sales') ensures that the measure respects slicers but still evaluates the full date range within the selected period.
2. STARTOFQUARTER('Sales'[Date]) determines the first date of the current quarter.
3. FILTER ensures that the sum includes all dates from the quarter start up to the current date in context.

Alternative Approach Using TIME INTELLIGENCE

If you have a separate Date Table, use DATESQTD for better time intelligence support:

RunningTotal_Quarterly =  
TOTALQTD( SUM('Sales'[Amount]), 'Date'[Date] )