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

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's avatar
talespin
Icon for Solution Sage rankSolution Sage
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      talespin
      Icon for Solution Sage rankSolution Sage
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

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