topic row minus previous row in DAX Commands and Tips

row minus previous row

Pan_Forex — Fri, 14 Jun 2024 13:54:52 GMT

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.

Re: row minus previous row

Kaviraj11 — Fri, 14 Jun 2024 14:09:07 GMT

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 )

Re: row minus previous row

Pan_Forex — Fri, 14 Jun 2024 15:08:49 GMT

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

Re: row minus previous row

Kaviraj11 — Fri, 14 Jun 2024 15:27:04 GMT

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

Re: row minus previous row

Pan_Forex — Fri, 14 Jun 2024 15:52:46 GMT

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

Re: row minus previous row

Kaviraj11 — Fri, 14 Jun 2024 16:00:29 GMT

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

Re: row minus previous row

Pan_Forex — Fri, 14 Jun 2024 18:12:50 GMT

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?

Re: row minus previous row

AntrikshSharma — Fri, 14 Jun 2024 20:34:32 GMT

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