Forum Discussion
Cummulative Prev Change
- 6 years ago
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.
NathanielCumulative = 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 ) - 6 years ago
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?
- 6 years ago
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]
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]
Thank you JDBuchanan and Nathan, for all you help.
Much appreciated 🙂