Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Average Running total each quarter interval

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 aut...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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

     

burakkaragoz's avatar
burakkaragoz
Icon for Super User rankSuper User
1 year ago

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:

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!
translation and formatting supported by AI

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago
    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's avatar
      danextian
      Icon for Super User rankSuper User
      1 year ago

      burakkaragoz another AI copy-paste?

       

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