Forum Discussion

Analitika's avatar
Analitika
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

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

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

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    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!

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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])

    • Analitika's avatar
      Analitika
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

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

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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!