Forum Discussion

qixue's avatar
qixue
Frequent Visitor
9 years ago
Solved

Calculate growth dynamically based on the date hierarcy

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)
)
Need Help
  • qixue's avatar
    qixue
    9 years ago

    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)
			)
		)
	)		
)

1 Reply

  • qixue's avatar
    qixue
    Frequent Visitor
    9 years ago

    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)
			)
		)
	)		
)