Forum Discussion
Anonymous
1 year agoNot applicable
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...
- 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
Anonymous
1 year agoNot applicable
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
Super User
1 year agoburakkaragoz 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.