Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Get Previous Value at Table visual by DAX

Hi there,   I have questions about getting the previous value at Table in order to calculate the change by percentage.   All fields at from the same table -STAGERAW. For Average of Days in Stage,...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    Please try this:

    I create a set of sample:

    Then create a measure:

    Measure = 
VAR _currentmonth = MAX('Table'[Month_Year])
	VAR _currentdata = CALCULATE(
		AVERAGE('Table'[TOTALYDAYSINSTAGE]),
		'Table'[Month_Year] = _currentmonth
	)
	VAR _previousmonth = CALCULATE(
		MAX('Table'[Month_Year]),
		FILTER(
			ALLSELECTED('Table'),
			'Table'[Month_Year] < _currentmonth
		)
	)
	VAR _previousdata = CALCULATE(
		AVERAGE('Table'[TOTALYDAYSINSTAGE]),
		'Table'[Month_Year] = _previousmonth
	)
	RETURN
		IF(
			_previousmonth <> BLANK(),
			(_currentdata - _previousdata) / _previousdata
		)

    The result is as follow:

    Best Regards,

    Zhengdong Xu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

talespin's avatar
talespin
Icon for Solution Sage rankSolution Sage
2 years ago

hi Anonymous 

 

Sharing both calculated column and measure

 

 

Calculated Column =
VAR _CurrStageName = TestTbl2[Stage Name]
VAR _CurrMth = TestTbl2[Month_Year]
VAR _CurrAvg = TestTbl2[AvgDaysInStage]
VAR _PrevMonth = IF(_CurrMth - 1 <= 0, 1, _CurrMth - 1)
VAR _PreviousVal = CALCULATE(VALUES(TestTbl2[AvgDaysInStage]), REMOVEFILTERS(TestTbl2), TestTbl2[Stage Name] = _CurrStageName, TestTbl2[Month_Year] = _PrevMonth )
RETURN DIVIDE( (_CurrAvg - _PreviousVal), _PreviousVal, BLANK() )
 

 

Measure =
VAR _CurrStageName = SELECTEDVALUE(TestTbl2[Stage Name])
VAR _CurrMth = SELECTEDVALUE(TestTbl2[Month_Year])
VAR _CurrAvg = SELECTEDVALUE(TestTbl2[AvgDaysInStage])
VAR _PrevMonth = IF(_CurrMth - 1 <= 0, 1, _CurrMth - 1)
VAR _PreviousVal = CALCULATE(VALUES(TestTbl2[AvgDaysInStage]), REMOVEFILTERS(TestTbl2), TestTbl2[Stage Name] = _CurrStageName, TestTbl2[Month_Year] = _PrevMonth)
RETURN DIVIDE( (_CurrAvg - _PreviousVal), _PreviousVal, BLANK())