Forum Discussion
Average Running total each quarter interval
- 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
Hi Anonymous
Use a dedicated dates table with a quarterkey that can be sorted chronologically and try this:
Qtr Avg Running Total =
// Get the latest date that has data, considering slicers (ALLSELECTED)
VAR LatestDateWithData =
CALCULATE (
LASTNONBLANK ( Dates[Date], [Total Revenue] ),
ALLSELECTED ( Dates )
)
// Get the month number of the latest date with data
VAR LatestMonthNumber =
MONTH ( LatestDateWithData )
// Get the YearQuarter key (formatted as "YYYYQQ") for the latest date with data
VAR LatestYearQuarter =
FORMAT ( LatestDateWithData, "YYYYQQ" )
// Get the YearQuarter key for the current row
VAR RowQuarter =
MAX ( Dates[YearQuarter] )
// Define the end months of each quarter
VAR QuarterEnds = { 3, 6, 9, 12 }
RETURN
IF (
// Check if the current row is the latest (still running) quarter
LatestYearQuarter = RowQuarter
&& NOT LatestMonthNumber
IN QuarterEnds && ISFILTERED ( Dates[YearQuarter] ),
0, // Show 0 for the latest incomplete quarter
CALCULATE (
// Calculate the running average up to the current quarter
AVERAGEX ( VALUES ( Dates[YearQuarter] ), [Total Revenue] ),
FILTER ( ALL ( Dates[YearQuarter] ), Dates[YearQuarter] <= RowQuarter )
)
)
Please see the attached pbix for the details.
danextian - Thanks for your response.
Basically we need to compare current quarter Vs previous 4 Quarter Average average price
example current quarter price 40 and last 4 quarter average = 60 i.e 240/4 (60,30,70,80)
revenue down = 20 so highlight as red color using conditional formatting.
I gone thru pbix file and my view outputs - Highlighted and should be numbers, column C as per DAX Formula
please suggest
- danextian1 year ago
Super User
You can change the RETURN statement to this:
RETURN IF ( // Check if the current row is the latest (still running) quarter LatestYearQuarter = RowQuarter && NOT LatestMonthNumber IN QuarterEnds && ISFILTERED ( Dates[YearQuarter] ), 0, // Show 0 for the latest incomplete quarter CALCULATE ( // Calculate the running average up to the current quarter AVERAGEX ( VALUES ( Dates[YearQuarter] ), [Total Revenue] ), DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -4, QUARTER ), REMOVEFILTERS ( Dates ) ) )REMOVEFILTERS ( Dates ) is not necessary if you have set your Dates/Calendar table as such.
- Anonymous1 year agoNot applicable
danextian - It works as one thing not working for running current quarter this will make as zero if current quarter is 2025-Q2 still we have 18 days left.
another one help for we are giving average above 4 Quarter in current quarter incase doesn't have 4 quarter then value should be 0 (example as follows)
DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -4, QUARTER ))example : should be value (average total)2023-01 -- 0 (zero)2023-02 ---0 (above 1 Quarter so no average make for 4 Quarter so zero)2023-03 --- 0 (above 2 Quarter so no average make for 4 Quarter so zero)2023-04 --- 0 (above 3 Quarter so no average make for 4 Quarter so zero)2024-01 - as per formula (above 4 Quarter) - condition fulfill and DAX Result2024-02 - as per formula (above 4 Quarter) - condition fulfill and DAX Result2024-03 -- as per formula (above 4 Quarter) - condition fulfill and DAX Result2024-04 -- as per formula (above 4 Quarter) - condition fulfill and DAX Result2025-01 -- as per formula (above 4 Quarter) - condition fulfill and DAX Result2025-02 -- 0 - Quarter yet to completeThanks- danextian1 year ago
Super User
This is confusing. You wanted to get the average of up to last 4 quarters before current in your previous repsonse but now you want them to return zero. This is also different form your initial example that took the average of the last 4 quarters including the current.