Performance issue to find Rolling52WeekAverage from the Measure value using by (SUMX & AVERAGEX)

[Anonymous]

Hi,

I am using the following two DAX formulas to find an average of Rolling52Weeks from the Measure value, the results are getting correctly but performance is very slow.  I just filtering the prior 52 weeks from the calendar and applying into SUMX/ AVERAGEX in order to get a result. 

Can anyone please help me if there is any way to replace the SUMX/ AVERAGEX for the below scenarios or can please confirm if I made any logical mistaken on the below DAX.

Appreciate if you join with me to get resolved.

Rolling52WeekAverage:=

var Filtervalue =
FILTER(ALL(CalendarWeekly),AND(CalendarWeekly[CalendarWeeklySkey] <=max(CalendarWeekly[CalendarWeeklySkey]),CalendarWeekly[FiscalWeekCloseDt] > max(CalendarWeekly[FiscalWeekCloseDt]) - 364 && Fact[Average_FlagNew] <> 3))

var Tempcnt =
COUNTROWS ( Filtervalue )

var rd = SUMX(Filtervalue ,Fact[Measure Value])

return DIVIDE(rd, Tempcnt )

Rolling52WeekAverage:=

var Filtervalue =
FILTER(ALL(CalendarWeekly),AND(CalendarWeekly[CalendarWeeklySkey] <=max(CalendarWeekly[CalendarWeeklySkey]),CalendarWeekly[FiscalWeekCloseDt] > max(CalendarWeekly[FiscalWeekCloseDt]) - 364 && Fact[Average_FlagNew] <> 3))

return AVERAGEX(FILTER(Filtervalue,IF(Fact[Average_FlagNew] <> 3 ,1)),Fact[Measure Value])

Regards,

Raj

[Fowmy · Accepted solution]

@Anonymous 

I am not sure if the SUMX or AVERAGEX causes the issue, check your data model with best practices https://powerbi.microsoft.com/en-us/blog/best-practice-rules-to-improve-your-models-performance/

Check the following modified measure.

Rolling52WeekAverage = 
var _skey = _CalendarWeekly[CalendarWeeklySkey]
var _close = _CalendarWeekly[FiscalWeekCloseDt]- 364
var Filtervalue =
    FILTER(
        ALL(CalendarWeekly[CalendarWeeklySkey],CalendarWeekly[FiscalWeekCloseDt]),
        CalendarWeekly[CalendarWeeklySkey] <=_skey &&
        CalendarWeekly[FiscalWeekCloseDt] > _close  && 
        [Average_FlagNew] <> 3
    )

var Tempcnt = COUNTROWS ( Filtervalue )
var rd = SUMX(Filtervalue ,[Measure Value])
    return 
    DIVIDE(rd, Tempcnt )

[Anonymous]

Your DAX logic works fine and it  is faster than earlier logic. 

Thank you so much Fowmy!!

Regards,

Rajkumar