Difference calculation per different rows in table in Power BI

[Analitika]

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]

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

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)

[Analitika]

Error near =_1 name not found 

[amitchandak]

@Analitika , Please share sample data and sample output in table format?

[Analitika]

Here is sample data:

Output data:

[amitchandak]

@Analitika ,

small chnage

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())

[amitchandak]

@Analitika , small chnage

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]

I have tried this query but seems it didn't help still getting wrong answer:

Seems something wrong. I expected to get 0.87. (Difference should be calculated like 3290-3289.13=0.87  but not like (0-3289.13=-3289.13)

[amitchandak]

@Analitika , Please share data in table format. So that I can check

[Analitika]

Is that format ok now?

[Analitika]

ID	OBJ_ID	DATE	CRE	DET	OPE_ID
2	rov1	2021-06-30	3290	0	30
2	rov1	2021-06-19	0	3289.13	50

Here is sample data.

Seems something wrong with that formula. Seems it calculating like this SUM_CRE-SUM_DEB per row not per different rows.

It should calculate 3290-3289.13. Not 0-3289.13.

[amitchandak]

@Analitika , Date is more in first record

so solution can be

Column =
VAR _0 = MINX(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())

or

Column =
VAR _1 = MAXX(FILTER( 'x'[ID]= EARLIER('x'[ID]) ),[cre])
return
if('x'[deb] <> 0,_1 - 'x'[deb], blank())

[Analitika]

@amitchandak With first query I am getting result like this:

The second query also wrong. In both cases I am getting wrong answers.

[amitchandak · Accepted solution]

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