Get Previous Value at Table visual by DAX

[Anonymous]

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, I use the TOTALYDAYSINSTAGE column, data type is whole number and select "average".

I hope to get the change by stagename. For example, Authorise NIL Memebers percentage change:

month_year       pct_change

2                       = (18.13 - 1)/1

3                       = (9.5 - 18.13) / 18.13

I've tried OFFSET and EARLIER but it shows (STAGERAW) Previous Value: EARLIER/EARLIST refers to an earlier row context which doesn't exist for both DAX.

method 1:

VAR currentvalue = AVERAGE(STAGERAW[TOTALDAYSINSTAGE])

VAR previousvalue = CALCULATE(

    currentvalue,

    ALL(STAGERAW),

    STAGERAW[BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER] = EARLIER(STAGERAW[BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER]))

RETURN previousvalue

method 2:

CALCULATE

    (firstnonblank(STAGERAW[TOTALDAYSINSTAGE], 1),

    FILTER(STAGERAW, STAGERAW[STAGENAME] = EARLIER(STAGERAW[STAGENAME])

    && STAGERAW[BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER] = EARLIER(STAGERAW[BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER])-1))

Any advise?

Many thanks,

Jessie

[Anonymous · Accepted solution]

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]

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

[Anonymous]

thank you for the solution. I have tried the measure as below:

Pct Change =

VAR _CurrStageName = SELECTEDVALUE(STAGERAW[STAGENAME])

VAR _CurrMth = SELECTEDVALUE(STAGERAW[BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER])

VAR _CurrAvg = SELECTEDVALUE(STAGERAW[TOTALDAYSINSTAGE])

VAR _PrevMonth = IF(_CurrMth - 1<=0, 1, _CurrMth - 1)

VAR _PreviousVal = CALCULATE(VALUES(STAGERAW[TOTALDAYSINSTAGE]), REMOVEFILTERS(STAGERAW), STAGERAW[STAGENAME] = _CurrStageName, STAGERAW[BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER] = _PrevMonth)

RETURN DIVIDE((_CurrAvg - _PreviousVal), _PreviousVal, BLANK())

It shows:

"a table of multiple values was supplied where a single value was expected in dax"

I guess it's probably the duplicate value for stage name? Or it's because of the REMOVEFILTERS function that cause 

VALUES(STAGERAW[TOTALDAYSINSTAGE] doesn't work?

Many thanks,

Jessie

 

[talespin]

hi @Anonymous ,

Please confirm that combination of STAGENAME and BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER is unique.

If possible please share pbix file with all sensitive data removed.

[Anonymous]

Hi Talespin,

I hope to attach the pbix file but it shows .pbix is not supported. where can i attach file?

Meanwhile, shall i use concatenatex to combination STAGENAME and BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER to create unique value?

Many thanks,

Jessie

[talespin]

Hi @Anonymous ,

"Meanwhile, shall i use concatenatex to combination STAGENAME and BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER to create unique value?"

No, please don't do that

Please try this and test results. I assumed the combination of STAGENAME and MONTH is unique, but it is clear that you are taking average for DAYSINSTAGE. Sorry for that.

Pct Change =

VAR _CurrStageName = SELECTEDVALUE(STAGERAW[STAGENAME])

VAR _CurrMth = SELECTEDVALUE(STAGERAW[BEREVEMENT_MONTH_NUMBER])

VAR _CurrAvg = CALCULATE( AVERAGE(STAGERAW[TOTALDAYSINSTAGE]) , REMOVEFILTERS(STAGERAW), STAGERAW[STAGENAME] = _CurrStageName, STAGERAW[BEREVEMENT_MONTH_NUMBER] = _CurrMth)

VAR _PrevMonth = IF(_CurrMth - 1<=0, 1, _CurrMth - 1)

VAR _PreviousVal = CALCULATE( AVERAGE(STAGERAW[TOTALDAYSINSTAGE]), REMOVEFILTERS(STAGERAW), STAGERAW[STAGENAME] = _CurrStageName, STAGERAW[BEREVEMENT_MONTH_NUMBER] = _PrevMonth)

RETURN DIVIDE((_CurrAvg - _PreviousVal), _PreviousVal, BLANK())

[Anonymous]

 the measure works but it doesn't pull the previous value. 

I change the RETURN to previousVal and please find the screenshot as reference.

Pct Change =

VAR _CurrStageName = SELECTEDVALUE(STAGERAW[STAGENAME])

VAR _CurrMth = SELECTEDVALUE(STAGERAW[BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER])

VAR _CurrAvg = CALCULATE(AVERAGE(STAGERAW[TOTALDAYSINSTAGE]),

                         REMOVEFILTERS(STAGERAW), STAGERAW[STAGENAME] = _CurrStageName, STAGERAW[BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER] = _CurrMth)

VAR _PrevMonth = IF(_CurrMth - 1<=0, 1, _CurrMth - 1)

VAR _PreviousVal = CALCULATE(AVERAGE(STAGERAW[TOTALDAYSINSTAGE]), REMOVEFILTERS(STAGERAW), STAGERAW[STAGENAME] = _CurrStageName, STAGERAW[BEREAVEMENTRAW.BEREVEMENT_MONTH_NUMBER] = _PrevMonth)

RETURN _PreviousVal

// RETURN DIVIDE((_CurrAvg - _PreviousVal), _PreviousVal, BLANK())

[talespin]

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