Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cummulative Prev Change

Good afternoon 

I am struggling with a DAX formula, and will appreciate some help

Want to create a formula for "Cummulative Prev Change" as per screenshot and following conditions

1) First two rows have to be zero

2) from 3rd row, the formula should get PreviousVersions's "Requested Value" + PreviousVersions's "Cummulative Prev Change" if

 

I tried following formula, to no effect 😞  

CummulativePrevChange2 = IF(PA_KEY[Version]>1,LOOKUPVALUE(PA_KEY[RequestedValue],PA_KEY[Version],PA_KEY[Version]-1,PA_KEY[PA#],PA_KEY[PA#]),0)
 
Attached are the sample file and screenshot of how the column should calculate values...
 
 
Thank you in advance for any advise 🙂
  • Hi Anonymous 
    The solution at this point is two columns.


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    Cumulative = 
    
    var _curIndex =PA_KEY[Index]
    var _minIndex = CALCULATE(MIN(PA_KEY[Index]),ALLEXCEPT(PA_KEY,PA_KEY[PA#]),PA_KEY[Index]<_curIndex)
    
    return
    
    IF(_curIndex = _minIndex || _curIndex = _minIndex+1,BLANK(), CALCULATE(sum(PA_KEY[RequestedValue]),ALLEXCEPT(PA_KEY,PA_KEY[PA#]),PA_KEY[Index]=_curIndex -1))

     

    Cumulative and previous = 
    VAR _curIndex = PA_KEY[Index]   //Get current Index from this row
    VAR _minIndex = CALCULATE ( MIN ( PA_KEY[Index] ), ALLEXCEPT ( PA_KEY, PA_KEY[PA#] ), PA_KEY[Index] < _curIndex )    //Get minimum index for this PA#                                
                                  
    VAR _prev3 = CALCULATE ( SUM ( PA_KEY[Cumulative] ),  ALLEXCEPT ( PA_KEY, PA_KEY[PA#] ), PA_KEY[Index] = _curIndex - 1 )   // Get the previous row amount from the Cumulative column                                
    
    RETURN
        IF (                                        //Return a blank if the current index is equal to the first two rows of this PA# else do the calc and add the previous row from the Cumulative column
            _curIndex = _minIndex
                || _curIndex = _minIndex + 1,
            BLANK (),
            CALCULATE (
                SUM ( PA_KEY[RequestedValue] ),
                ALLEXCEPT (
                    PA_KEY,
                    PA_KEY[PA#]
                ),
                PA_KEY[Index] = _curIndex - 1
            ) + _prev3
        )

     

  • Anonymous 

    Just this portion is the measure and it relies on the index column Nathaniel_C  added to your sample table.  

    Column = 
    VAR _MinIndex = CALCULATE ( MIN ( PA_KEY[Index] ), ALLEXCEPT ( PA_KEY, PA_KEY[PA#] ) )
    VAR _RowIndex = [Index]
    RETURN CALCULATE ( SUM ( PA_KEY[RequestedValue] ), ALLEXCEPT ( PA_KEY, PA_KEY[PA#] ), PA_KEY[Index] > _MinIndex && PA_KEY[Index] < _RowIndex )

     Are you able to add an index column to your source data that will properly order the lines?

  • The problem with using the original table is you don't know what order the lines are going to come in unless you give it an order by.  You could add the index on the SQL side as well, something like this.

     

    SELECT
    	PA_Key,
    	Version,
    	[Invoice LINE],
    	CONTRACTLINEVALUE,
    	REQUESTEDVALUE,
    	PA#,
    	ROW_NUMBER() OVER( ORDER BY ( SELECT 0 ) ) AS RowIndex
    FROM Table
    ORDER BY
    	PA_Key,
    	Version,
    	[Invoice LINE]

     

10 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      thank you Nathan,

      Kindly can you share the DAX for your column?

      thank you

      -Usman

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion

        Hi Anonymous 
        The solution at this point is two columns.


        Let me know if you have any questions.

        If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
        Nathaniel

        Cumulative = 
        
        var _curIndex =PA_KEY[Index]
        var _minIndex = CALCULATE(MIN(PA_KEY[Index]),ALLEXCEPT(PA_KEY,PA_KEY[PA#]),PA_KEY[Index]<_curIndex)
        
        return
        
        IF(_curIndex = _minIndex || _curIndex = _minIndex+1,BLANK(), CALCULATE(sum(PA_KEY[RequestedValue]),ALLEXCEPT(PA_KEY,PA_KEY[PA#]),PA_KEY[Index]=_curIndex -1))

         

        Cumulative and previous = 
        VAR _curIndex = PA_KEY[Index]   //Get current Index from this row
        VAR _minIndex = CALCULATE ( MIN ( PA_KEY[Index] ), ALLEXCEPT ( PA_KEY, PA_KEY[PA#] ), PA_KEY[Index] < _curIndex )    //Get minimum index for this PA#                                
                                      
        VAR _prev3 = CALCULATE ( SUM ( PA_KEY[Cumulative] ),  ALLEXCEPT ( PA_KEY, PA_KEY[PA#] ), PA_KEY[Index] = _curIndex - 1 )   // Get the previous row amount from the Cumulative column                                
        
        RETURN
            IF (                                        //Return a blank if the current index is equal to the first two rows of this PA# else do the calc and add the previous row from the Cumulative column
                _curIndex = _minIndex
                    || _curIndex = _minIndex + 1,
                BLANK (),
                CALCULATE (
                    SUM ( PA_KEY[RequestedValue] ),
                    ALLEXCEPT (
                        PA_KEY,
                        PA_KEY[PA#]
                    ),
                    PA_KEY[Index] = _curIndex - 1
                ) + _prev3
            )