row minus previous row

[Pan_Forex]

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	employe	points	output
200	A	100	-10
200	B	150	10
200	C	130	50
190	A	110
190	B	140	40
190	C	80	30
180	C	50
180	B	100

output =

VAR CurrentPoints = 'Table'[points]

VAR PreviousPoints =

    CALCULATE(

        MAX('Table'[points]),

        FILTER(

             'Table',

            'Table'[emloye] = EARLIER('Table'[emloye]) &&

            'Table'[id] < EARLIER('Table'[id])

            )

        )

RETURN

    IF(ISBLANK('Table'[id]), BLANK(), CurrentPoints - PreviousPoints)

My function returns different values than the output from the table as if it was looking for the maximum value from all rows.

[AntrikshSharma · Accepted solution]

@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]

How large is the dataset?

Here are the articles to resolve the memory issues.

 How to Fix Memory Allocation Error in Power BI Desktop - Monocroft

[Pan_Forex]

It is not large, as it has about 100,000 records

[Kaviraj11]

Would need more information to understand the root cause. Are you getting the error after creating a calculated column? and make sure the dataset is sorted as well

[Pan_Forex]

I solved this problem and as you can see in the picture sometimes it counts right and sometimes wrong. What could this be due to?

[Kaviraj11]

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]

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