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.

Anonymous's avatar
Anonymous
Not applicable
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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

it works correctly! i change the RETURN to _previousdata and get the below correct previous value:

 

 can i ask about the operation process for this DAX?

 

when we create VAR _currentmonth = 
= MAX( STAGERAW[BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER]), does it runs by row? so it gets the MAX month by row then it gets month 1, then month 2, then month 3, etc