Average Running total each quarter interval

Assuming you have a date table and if there is not a quarter column in it you need to add one

QuarterID = 
"Q" & FORMAT(QUARTER('Date'[Date]), "0") & "-" & FORMAT('Date'[Date], "YYYY")

Use a measure or variable to dynamically detect the current quarter:

CurrentQuarter =
VAR TodayDate = TODAY()
RETURN
    FORMAT(TodayDate, "YYYY") & "-Q" & FORMAT(TodayDate, "Q")

Running Average Excluding Current Quarter

Average Running Total =
VAR CurrentQ = FORMAT(TODAY(), "YYYY") & "-Q" & FORMAT(TODAY(), "Q")
VAR SelectedQ = SELECTEDVALUE('Date'[QuarterLabel])
VAR IsPast = SelectedQ < CurrentQ

RETURN
IF (
    IsPast,
    CALCULATE (
        AVERAGEX (
            DATESQTD('Date'[Date]),
            CALCULATE(SUM('SalesTable'[Sales]))
        ),
        FILTER (
            ALLSELECTED('Date'),
            'Date'[QuarterLabel] <= SelectedQ &&
            'Date'[QuarterLabel] < CurrentQ
        )
    ),
    0
)

Please mark this post as a solution if it helps you. Appreciate Kudos.

Hi Anonymous ,

You can achieve this requirement in DAX with a measure that checks if the current quarter is incomplete (i.e., the latest quarter and still running), and returns zero for it; otherwise, it will calculate the average running total as usual.

Suggested DAX Measure:

Average Running Total =
VAR MaxQuarter = MAX('Table'[Quarter])
VAR CurrentQuarter = SELECTEDVALUE('Table'[Quarter])
VAR IsCurrent = CurrentQuarter = MaxQuarter
RETURN
    IF(
        IsCurrent,
        0,  // Show 0 for the latest (still running) quarter
        CALCULATE(
            AVERAGEX(
                FILTER(
                    ALL('Table'),
                    'Table'[Quarter] <= CurrentQuarter
                ),
                'Table'[Sales]
            )
        )
    )

• Replace 'Table' and [Sales] with your actual table and column names if different.
• This measure shows 0 for the most recent quarter, and once the next quarter’s data is added, the previous one will become part of the running total as expected.

If you want the logic to be based on a date field instead of a quarter column, or need the measure to update dynamically when new data is available, let me know!

Hope this helps!
translation and formatting supported by AI

Hi,

Does your actual data have a proper date column?  If yes, then share that data in a format that can be pasted in an MS Excel file.  Also, would Q1 be Apr-Jun or Jan-Mar?

Hi Anonymous 

Use a dedicated dates table with a quarterkey that can be sorted chronologically and try this:

Qtr Avg Running Total = 
// Get the latest date that has data, considering slicers (ALLSELECTED)
VAR LatestDateWithData =
    CALCULATE (
        LASTNONBLANK ( Dates[Date], [Total Revenue] ),
        ALLSELECTED ( Dates )
    )
// Get the month number of the latest date with data
VAR LatestMonthNumber =
    MONTH ( LatestDateWithData )
// Get the YearQuarter key (formatted as "YYYYQQ") for the latest date with data
VAR LatestYearQuarter =
    FORMAT ( LatestDateWithData, "YYYYQQ" )
// Get the YearQuarter key for the current row
VAR RowQuarter =
    MAX ( Dates[YearQuarter] )
// Define the end months of each quarter
VAR QuarterEnds = { 3, 6, 9, 12 }
RETURN
    IF (
        // Check if the current row is the latest (still running) quarter
        LatestYearQuarter = RowQuarter
            && NOT LatestMonthNumber
                IN QuarterEnds && ISFILTERED ( Dates[YearQuarter] ),
        0, // Show 0 for the latest incomplete quarter
        CALCULATE (
            // Calculate the running average up to the current quarter
            AVERAGEX ( VALUES ( Dates[YearQuarter] ), [Total Revenue] ),
            FILTER ( ALL ( Dates[YearQuarter] ), Dates[YearQuarter] <= RowQuarter )
        )
    )

Please see the attached pbix for the details.

Hi Anonymous,
I wanted to check if you had the opportunity to review the information provided by danextian andrewsommer burakkaragoz . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution so other members can easily find it.
Thank you.