Hi All,
I have a Matrix visual that is used to calculate various measures which when published to service gives an error "Visual has exceeded available resources"
I have narrowed down to 3 measures whose performance is causing the matrix to slow down.
Last Period of Favourable Var (In 1 Year) =
VAR __YearShift = 1 //max number of years to look back
VAR __MaxDate = MAX('Calendar'[Date]) //latest date
VAR __MinDate = DATE(YEAR(__MaxDate) - __YearShift, MONTH(__MaxDate), 1) //the first date to appear in calendar
VAR __FilteredCal = FILTER(ALL('Calendar'), 'Calendar'[Date] >= __MinDate && 'Calendar'[Date] <= __MaxDate) //calendar filtered by min and max dates defined above
VAR __LastPeriod = CALCULATE(LASTNONBLANK('Calendar'[Date], [Positive Var]), __FilteredCal) //Calculates last period
RETURN IF(ISBLANK(__LastPeriod), __MinDate, __LastPeriod) //returns min period if there is blank because we only look back that far
# of Months Unfavourable Var =
Var x=[Last Period of Favorable Var (In 1 year)]
Var y=[MaxDate]
return(DATEDIFF(x,y,MONTH))
Sum in period =
CALCULATE([Gross Margin Var],DATESBETWEEN('Calendar'[Date],[Next Month Same Day],MAX('Calendar'[Date])))
/*
Next Month Same Day =
VAR CurrentDate = [Last Period of Favorable Var (In 1 year)]
VAR FirstDayOfNextMonth = EOMONTH(CurrentDate,0) + 1
VAR DaysToSameDay = DAY(CurrentDate) - 1
RETURN FirstDayOfNextMonth + DaysToSameDay
*/
Can someone please help me optimize these codes to get better DAX query performance
