Lowest processor way to calc a trailing 4 quarter sum

[charleshale]

Hi.   I'm wondering if anyone has opinions on the least processor-intensive way to do basic trailing X period measures in DAX for very large tables.

Ie lets say I want to do a simple CALCULATE ( /measure/,   /prior 4 quarters excluding current quarter/),  any thoughts on whether you'd use DATESINPERIOD, EDATE,  VAR with DimDate[Date] < _date, etc)?

I'm working with a 20m row table looking simply to reduce processor load

Thank you for any thoughts.

[amitchandak]

I did not get it completely.

But with time intelligence and date calendar, we can get current prior and training qtr

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos.

[charleshale · Accepted solution]

Thanks, @amitchandak .   Maybe best I handle this with semi-actual-DAX.

Any thoughts on which would be faster computationally for a trailing 4 quarters measurement of the following?

Trailing4Q (homemade) =

var _Lastq1 = CALCULATE([__measure__],PARALLELPERIOD(DimDate[Date],-1,QUARTER))
var _Lastq2 = CALCULATE([__measure__],PARALLELPERIOD(DimDate[Date],-2,QUARTER))
var _Lastq3 = CALCULATE([__measure__],PARALLELPERIOD(DimDate[Date],-3,QUARTER))
var _Lastq4 = CALCULATE([__measure__],PARALLELPERIOD(DimDate[Date],-4,QUARTER))
var _Trailng4Q = _Lastq1+_Lastq2+_Lastq3+_Lastq4

RETURN
_Trailng4Q 

vs this one suggested by QuickMeasure

Trailing4Q (QuickMeasure) = 

var _Trail4Q = 
IF(
	ISFILTERED('DimDate'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	var __LAST_DATE = DATEADD(ENDOFQUARTER('DimDate'[Date]),-3,MONTH)
	var __DATE_PERIOD =
		DATESBETWEEN(
			'DimDate'[Date],
			STARTOFQUARTER(DATEADD(__LAST_DATE, -3, QUARTER)),
			__LAST_DATE
		)
	RETURN
		sumx(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('DimDate'),
					'DimDate'[Date].[Year],
					'DimDate'[Date].[QuarterNo],
					'DimDate'[Date].[Quarter]
				),
				__DATE_PERIOD
			),
			CALCULATE(
				[_measure_],
				ALL(
					'DimDate'[Date].[MonthNo],
					'DimDate'[Date].[Month],
					'DimDate'[Date].[Day]
				)
			)
		)
)

RETURN 
_Trail4Q

 Note for PBIX geeks: My favorite formulation is Marco Russo's @SQLBI, which is the following -- super fast and elegant as usual from SQLBI -- but I couldnt see any easy way to map it to a measure -- ie it's built for a table

Months12M = 
CALCULATE (
    CALCULATE ( COUNTROWS ( VALUES ( Calendar[MonthName] ) ), Sales ),
    DATESBETWEEN (
        Calendar[FullDate],
        NEXTDAY ( SAMEPERIODLASTYEAR (LASTDATE ( Calendar[FullDate] ) ) ),
        LASTDATE ( Calendar[FullDate] )
    )
)

[charleshale]

Note - the (homemade) function is testing as about 3x faster than the QuickMeasure version, perhaps ironically!  

[amitchandak]

Thanks for sharing. Kudos !!

[Greg_Deckler]

Difficult to be specific without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490