I need to optomize my 3 month rolling average measure. It is not loading the visual.

[sidvix917]

Hello,

I have been working on this report for some time and have used this forum to resolve a few issues with my successful measures! I am in the final phase of publishing the report; however, one of the visualizations displays the error 'Visual has exceeded the available resources', preventing it from loading. I have determined that one of the measures is consuming a great deal of memory, and that this measure is linked to multiple other measures. I have listed all the measures in sequence, beginning with the main one.

3 Month Rolling Average =

CALCULATE(AVERAGEX(VALUES(Dates[Month & Year]),[Last Date with Data]),DATESINPERIOD(Dates[Date],MAX(Dates[Date]),-3,MONTH))

Last Date with Data =

if(MIN(Dates[Date])>[Date till which data is available],BLANK(),[Last Non Blank Value])

Date till which data is available = MAXX(ALL(Dates),[Date Value])

Date Value = MAX(Query1[observations.date])

Last Non Blank Value =

Var Last_Date =

CALCULATE(MAX(Dates[Date]),

FILTER(ALL(Dates), Dates[Date] <= MAX(Dates[Date]) &&

Query1[Total Sales Values] <> 0))

return

CALCULATE([Sales Value], FILTER(ALL(Dates),Dates[Date] = Last_Date))

Total Sales Values = CALCULATE(sum(Query1[observations.value]),USERELATIONSHIP(Query1[observations.date],Dates[Date]))

Sales Value = SUM(Query1[observations.value])

I would appreciate assistance in optimizing this. Thank you very much.

[amitchandak]

@sidvix917 , Try window function can help

example

Rolling 3 = CALCULATE([Net], WINDOW(-2,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[sidvix917]

Hi Amit,

What are you referring to [Net]? And do I discard all other measures that I have mentioned?

[sidvix917]

The above window function you had given did not work. I am pasting the measure that I was using once again. It does not load the visual when I Publish the report online. It works on the Power BI desktop, but it is prolonged.

Last Non Blank Value =

Var Last_Date =

CALCULATE(MAX(Dates[Date]),

FILTER(ALL(Dates), Dates[Date] <= MAX(Dates[Date]) &&

Query1[Total Sales Values] <> 0))

return

CALCULATE([Sales Value], FILTER(ALL(Dates),Dates[Date] = Last_Date))

3 Month Rolling Average =

CALCULATE(AVERAGEX(VALUES(Dates[Month & Year]),if(MIN(Dates[Date])>MAXX(ALL(Dates),MAX(Query1[observations.date])),BLANK(),[Last Non Blank Value])),DATESINPERIOD(Dates[Date],MAX(Dates[Date]),-3,MONTH))