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
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
1 year agoCommunity Support
Hi Anonymous,
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.