How to calculate difference between values in same table but in different rows and columns Power BI

[Analitika]

Hello,

I would like ask how to properly write DAX formula. Because calculating the difference between the values I am getting wrong answers. For example, sample data:

My result after calculation:

But I have expected this result:

So result differs from 0.87. (-3289.13!=0.87)

So seems something wrong with my query.

Here is my formula:

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

[v-kelly-msft · Accepted solution]

Hi  @Analitika ,

I would recommend you first transform your data in power query using below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUSrKLzMEUmb6xgb6RgZGILaxkaUBkAJhYwOlWB00hYaWMIVgFUYWlnqGxkCWKVBtLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, OBJ_ID = _t, DATE = _t, CRE = _t, DET = _t, OPE_ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"OBJ_ID", type text}, {"DATE", type date}, {"CRE", Int64.Type}, {"DET", type number}, {"OPE_ID", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "OBJ_ID", "DATE"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"ID", "DATE"}, {{"OBJ_ID", each List.Max([OBJ_ID]), type nullable text}, {"Value", each List.Max([Value]), type number}})
in
    #"Grouped Rows"

And you will see:

Then create a calculated column as below:

Difference = 
var _mindate=CALCULATE(MIN('Table'[DATE]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[DATE]>EARLIER('Table'[DATE])))
var _maxvalue=CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[DATE]=_mindate))
Return
IF(_maxvalue=BLANK(),BLANK(),_maxvalue-'Table'[Value])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[amitchandak]

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

[Analitika]

Hello, is possible to make filter that ID will be like 5000 because same object has value with id 5000