Forum Discussion

Pan_Forex's avatar
Pan_Forex
Icon for Helper III rankHelper III
2 years ago
Solved

row minus previous row

Hello, I am trying to count the difference in points between the latest ID and the previous one for each employee. I have a very simple bug somewhere and I can't find it...  id         emplo...
  • AntrikshSharma's avatar
    AntrikshSharma
    2 years ago

    Pan_Forex 

    Total Points = SUM ( pan[points] )
    Output = 
VAR PreviousValue = 
    CALCULATE ( 
        [Total Points],
        OFFSET ( 
            -1,
            ALL ( pan[employee], pan[id] ),
            ORDERBY ( pan[id], ASC ),
            PARTITIONBY ( pan[employee] )
        )
    )
VAR CurrentValue = 
    [Total Points]
VAR GroupingByID = 
    ISINSCOPE ( pan[id] )
VAR Result = 
    IF ( 
        NOT ISBLANK ( PreviousValue )
            && GroupingByID,
        CurrentValue - PreviousValue
    )
RETURN
    Result

     

Kaviraj11's avatar
Kaviraj11
Icon for Solution Sage rankSolution Sage
2 years ago

Hi,

 

Create a new column to calculate the difference in points between the current row and the previous row for the same employee:

Points Difference =
VAR CurrentPoints = [points]
VAR PreviousPoints =
    CALCULATE (
        MAX ( 'Table'[points] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[id] < EARLIER ( 'Table'[id] )
            && 'Table'[employee] = EARLIER ( 'Table'[employee] )
        )
    )
RETURN
IF ( NOT ISBLANK ( PreviousPoints ), CurrentPoints - PreviousPoints )

 

  • Pan_Forex's avatar
    Pan_Forex
    Icon for Helper III rankHelper III
    2 years ago

    Thanks for your answer 🙂 It should work but yet a message about insufficient memory appears.