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...
  • 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