Forum Discussion
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 | 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.
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
7 Replies
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] )))RETURNIF ( NOT ISBLANK ( PreviousPoints ), CurrentPoints - PreviousPoints )Thanks for your answer 🙂 It should work but yet a message about insufficient memory appears.
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 ResultHow large is the dataset?
Here are the articles to resolve the memory issues.
How to Fix Memory Allocation Error in Power BI Desktop - Monocroft
