Calculate growth dynamically based on the date hierarcy

[qixue]

New to PowerBI. Thank you for your help

I was able to mimic the "new quick measure" utility to caculate the daily sales growth as below. My questions is how to make it dynamic so that if my date hierarchy rolls up to month or year, the growth will be calculated accordingly?

Avg Daily Sales Growth = 
IF(
	ISFILTERED('Date'[Date]),
			VAR _PREV = CALCULATE(
			SUM('Raw Data'[Fin Sales Amt])/DISTINCTCOUNT('Raw Data'[Sales Date]),
            DATEADD('Date'[Date], -1, DAY)
			)
RETURN
	DIVIDE(SUM('Raw Data'[Fin Sales Amt])/DISTINCTCOUNT('Raw Data'[Sales Date]) - _PREV, _PREV)
)

[qixue · Accepted solution]

It worked!!

Avg Daily Sales Growth = 
IF(
	ISFILTERED('Date'[Date].[Day]),
	VAR _PREV_DAY = CALCULATE(
		SUM('Raw Data'[Fin Sales Amt])/DISTINCTCOUNT('Raw Data'[Sales Date]),
		DATEADD('Date'[Date], -1, DAY)
	)			
	RETURN
		DIVIDE(SUM('Raw Data'[Fin Sales Amt])/DISTINCTCOUNT('Raw Data'[Sales Date]) - _PREV_DAY, _PREV_DAY),
	IF(
		ISFILTERED('Date'[Date].[Month]),
		VAR _PREV_MONTH = CALCULATE(
			SUM('Raw Data'[Fin Sales Amt])/DISTINCTCOUNT('Raw Data'[Sales Date]),
            DATEADD('Date'[Date], -1, MONTH)
			)			
		RETURN
			DIVIDE(SUM('Raw Data'[Fin Sales Amt])/DISTINCTCOUNT('Raw Data'[Sales Date]) - _PREV_MONTH, _PREV_MONTH),
		IF(
			ISFILTERED('Date'[Date].[Quarter]),
			VAR _PREV_QUARTER = CALCULATE(
				SUM('Raw Data'[Fin Sales Amt])/DISTINCTCOUNT('Raw Data'[Sales Date]),
				DATEADD('Date'[Date], -1, QUARTER)
			)			
			RETURN
				DIVIDE(SUM('Raw Data'[Fin Sales Amt])/DISTINCTCOUNT('Raw Data'[Sales Date]) - _PREV_QUARTER, _PREV_QUARTER),
			IF(
				ISFILTERED('Date'[Date].[Year]),
				VAR _PREV_YEAR = CALCULATE(	
					SUM('Raw Data'[Fin Sales Amt])/DISTINCTCOUNT('Raw Data'[Sales Date]),
					DATEADD('Date'[Date], -1, YEAR)
				)			
				RETURN
					DIVIDE(SUM('Raw Data'[Fin Sales Amt])/DISTINCTCOUNT('Raw Data'[Sales Date]) - _PREV_YEAR, _PREV_YEAR)
			)
		)
	)		
)