Rolling Average on a Measure using Direct Query

[Femi-0]

Hello,

We have been using the formula below, to calculate the 3 month moving average of the 80th percentile of the time to close cases per month. 

However, now that we have switched from importing the data to DirectQuery, the DAX no longer works, any support with understanding what has changed and an alternative DAX would be appreciated:

CALCULATE(

                      AVERAGEX(completed_cases
                                               ,PERCENTILE.INC(completed_cases[Duration],0.85)
                                               ,DATESINPERIOD(Date[Date],ENDOFMONTH(Date[Date],-3,MONTH)
                       ,completed_cases[flagged] == "No")

For context, we direct query a seperate date table, and have a 1:* relationship between the Date table and the completed_cases table.

[AntoineW · Accepted solution]

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

[Femi-0]

@AntoineW , we suspected this was the case but threw the hail Mary to see...appreciate you spelling out the limitations, thank you!