Rolling Average on a Measure using Direct Query

Hello Femi-0,

The root issue is that DirectQuery mode doesn’t support non-delegable operations such as:

• PERCENTILE.INC or PERCENTILEX.INC (statistical iterator functions)

• Complex table-iteration logic (AVERAGEX over a non-grouped table with nested aggregation)

• DATESINPERIOD combined with implicit column iteration outside aggregation context

In Import mode, Power BI’s VertiPaq engine can materialize the entire dataset in memory and perform row-by-row percentile calculations.
In DirectQuery mode, Power BI must translate DAX to SQL — and most statistical functions (like PERCENTILE.INC) have no SQL equivalent.
So the engine simply throws an unsupported function error or returns blank.

Option : Pre-aggregate percentiles in the source (best practice)

Since SQL engines can’t calculate percentiles dynamically via DAX, push the calculation to your data source view or stored procedure:

SELECT
DateKey,
PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY Duration) AS P80_Duration
FROM Completed_Cases
WHERE Flagged = 'No'
GROUP BY DateKey;

Then import or DirectQuery this pre-aggregated result and use a simple rolling average DAX : 

P80_3MonthAvg =
AVERAGEX(
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH),
CALCULATE(AVERAGE('P80 View'[P80_Duration]))
)

Hope it can help you !

Best regards,

Antoine