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, 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'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.

8 Replies

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

     

  • 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

  • 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