Average Running total each quarter interval

[RajK2]

I need running total for each quarter interval DAX Formula

result shown in the table "average running total"

2025-Q2 still running quarter show value as zero once this quarter is completed then automatically change to as usual running total, suggest

Quarter	Sales	average running total
2023-Q1	30	30
2023-Q2	20	25
2023-Q3	10	20
2023-Q4	30	22.5
2024-Q1	34	23.5
2024-Q2	45	29.75
2024-Q3	34	35.75
2024-Q4	32	36.25
2025-Q1	45	39
2025-Q2	50	0

[v-saisrao-msft]

Hi @RajK2,
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.

[danextian]

Hi @RajK2 

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.

[RajK2]

@danextian  - Thanks for your response.

Basically we need to compare current quarter Vs previous 4 Quarter Average average price

example current quarter price 40 and last 4 quarter average = 60 i.e 240/4  (60,30,70,80)

revenue down = 20 so highlight as red color using conditional formatting.

I gone thru pbix file and my view outputs - Highlighted and should be numbers, column C as per DAX Formula

please suggest

[danextian]

You can change the RETURN statement to this:

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] ),
            DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -4, QUARTER ),
            REMOVEFILTERS ( Dates )
        )
    )

REMOVEFILTERS ( Dates ) is not necessary if you have set your Dates/Calendar table as such. 

Set and use date tables in Power BI Desktop 

[RajK2]

@danextian - It works as one thing not working for running current quarter this will make as zero if current quarter is 2025-Q2 still we have 18 days left.

another one help for we are giving average above 4 Quarter in current quarter incase doesn't have 4 quarter then value should be 0 (example as follows)

DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -4, QUARTER ))

example :   should be value (average total)

2023-01 -- 0 (zero)

2023-02 ---0 (above 1 Quarter so no average make for 4 Quarter so zero) 

2023-03 --- 0 (above 2 Quarter so no average make for 4 Quarter so zero)

2023-04 --- 0 (above 3 Quarter so no average make for 4 Quarter so zero)

2024-01  - as per formula (above 4 Quarter) - condition fulfill and DAX Result

2024-02  - as per formula (above 4 Quarter) - condition fulfill and DAX Result

2024-03 -- as per formula (above 4 Quarter) - condition fulfill and DAX Result

2024-04 -- as per formula (above 4 Quarter) - condition fulfill and DAX Result

2025-01 -- as per formula (above 4 Quarter) - condition fulfill and DAX Result

2025-02 -- 0 - Quarter yet to complete

Thanks

[danextian]

This is confusing. You wanted to get the average of up to last 4 quarters before current in your previous repsonse but now you want them to return zero. This is also different form your initial example that took the average of the last  4 quarters including the current.

[RajK2]

@danextian apologies for confusion, treat as this is final requirement as some internal need changes hence my need get changed slightly DAX Formula, suggest on this.

[v-saisrao-msft]

Hi @RajK2,

I hope you had a chance to review the solution shared by @danextian . If it addressed your issue, please consider accepting it as the solution — it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

Thank you.

[danextian · Accepted solution]

Try this:

Qtr Avg Running Total 3 = 
// Get latest visible date with data
VAR LatestDateWithData =
    CALCULATE (
        LASTNONBLANK ( Dates[Date], [Total Revenue] ),
        ALLSELECTED ( Dates )
    ) // Get earliest YearQuarter
VAR EarliestQtr =
    CALCULATE ( MIN ( Dates[YearQuarter] ), ALL ( Dates ) )
VAR LatestMonthNumber =
    MONTH ( LatestDateWithData ) // Get month number of latest date
VAR QuarterNumber4Start =
    EDATE (
        CALCULATE ( MINX ( STARTOFQUARTER ( Dates[Date] ), [Date] ), ALL ( Dates ) ),
        3 * 3
    )
VAR LatestYearQuarter =
    FORMAT ( LatestDateWithData, "YYYY0Q" ) // Get YearQuarter key of latest date
VAR RowQuarter =
    MAX ( Dates[YearQuarter] ) // Get YearQuarter key for current row
VAR QuarterEnds = { 3, 6, 9, 12 } // Quarter end months
VAR StartOfQtr =
    MINX ( STARTOFQUARTER ( Dates[Date] ), [Date] )
VAR StartOfPrevQtr = StartOfQtr - 1 // Day before start of current quarter
VAR _Result =
    IF (
        HASONEVALUE ( Dates[YearQuarter] ),
        SWITCH (
            TRUE (),
            StartOfQtr <= QuarterNumber4Start, 0, // Show 0 if earliest quarter
            LatestYearQuarter = RowQuarter
                && NOT LatestMonthNumber
                    IN QuarterEnds && ISFILTERED ( Dates[YearQuarter] ), 0, // Show 0 if latest incomplete quarter
            CALCULATE (
                AVERAGEX (
                    VALUES ( Dates[YearQuarter] ),
                    [Total Revenue]
                ),
                DATESINPERIOD ( Dates[Date], StartOfPrevQtr, -4, QUARTER ),
                REMOVEFILTERS ( Dates )
            )
        )
    )
RETURN
    _Result

[Ashish_Excel]

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?

[burakkaragoz]

Hi @RajK2 ,

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:

dax

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!

[RajK2]

result showing as zero :

Running Total Quarterly =

VAR MaxQuarter = MAX('Calendar'[YYYY-QQ])

VAR CurrentQuarter = SELECTEDVALUE('Calendar'[YYYY-QQ])

VAR IsCurrent = CurrentQuarter = MaxQuarter

RETURN

    IF(

        IsCurrent,

        0,  // Show 0 for the latest (still running) quarter

        CALCULATE(

            AVERAGEX(

                FILTER(

                    ALL('Calendar'),

                    'Calendar'[YYYY-QQ] <= CurrentQuarter

                ),

                [value]

            )

        )

    )

[danextian]

@burakkaragoz another AI copy-paste?

@RajK2  That is because MaxQuarter and CurrentQuarter return the same value in the current row,  IsCurrent is TRUE and thus the condition returns zero.

[andrewsommer]

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.