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

     

andrewsommer's avatar
andrewsommer
Super User
1 year ago

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.