Forum Discussion

Icon for Post Prodigy rank

Post Prodigy

4 years ago

Solved

Difference calculation per different rows in table in Power BI

Hello,

I would like to ask how to calculate difference between values per different rows in same table.

I have this data:

I expect getting result something like this. We are having additional column Difference with a value 2000 (8000-6000).

I wrote DAX query as follows:

Column = VAR _0 = MAXX(FILTER('x','x'[date]<EARLIER('x'[date)),'x'[cre]) VAR _3 = _0-CALCULATE(MAX(x'[deb])) RETURN _3

But I am getting wrong answers.

I am getting wrong answers:

amitchandak
4 years ago
Analitika , Please find the attached file

one of the two

Calc 1 =
var _1 = maxx(FILTER('Table', [ID] =EARLIER('Table'[ID]) && [CRE]<>0) ,[CRE])
return if([DET] <>0, _1 -[DET])

Calc 12 =
var _1 = maxx(FILTER('Table', [ID] =EARLIER('Table'[ID]) && [CRE]<>0 && [DATE] >EARLIER('Table'[DATE]) ) ,[CRE])
return if([DET] <>0, _1 -[DET])
Value Diff.pbix40 KB

14 Replies

amitchandak
Super User
4 years ago
Analitika , try a new column like

Column =
VAR _0 = MAXX(FILTER('x','x'[date]<EARLIER('x'[date]) && 'x'[ID]<EARLIER('x'[ID])),[date])
VAR _1 = MAXX(FILTER('x','x'[date] =_1 && 'x'[ID]<EARLIER('x'[ID]) ),[cre])
return
if('x'[deb] <> 0,_1 - 'x'[deb], blank())
- Analitika
  Post Prodigy
  4 years ago
  Error near =_1 name not found
  - amitchandak
    Super User
    4 years ago
    Analitika , Please share sample data and sample output in table format?
- Analitika
  Post Prodigy
  4 years ago
  Also wrong answer because I am getting
  When having
  CRE and DEB. I expected to get 3290-3289.13=0.87 (0.87!=-3289.13)