Forum Discussion

marsclone's avatar
marsclone
Helper IV
3 years ago

Calculate next row minus current row?

I've searched, but didn't find the answer on this question?

I want to calculate the next value minus the current value from the column "Breken VSI totaal".

Is that possible?

 

All columns are measures in this model.

Thank you.

 

 

Need Help

7 Replies

  • adudani's avatar
    adudani
    Memorable Member
    3 years ago

    hi marsclone ,

     

    create a blank query and copy paste the following code in the advanced editor:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NU5BDsMwDPpLzpVlp0mbHNdu7f5Q5f/fGDjeARAGIT9POrSmJb2AVRpYxYLHwnSDOwC2spTgme1wJ0Ct0oNnxq03YCrblPaXWWD9w7komHAmZ4gXTGEvrhd/qRUvQGLBeL15qp70JnnKGgXaLwu7L/TuP5pqGuMH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nr = _t, GradTIE = _t, #"%Zeef  total" = _t, #"BreKen Vsi" = _t, #"BreKen Vsi total" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Nr", type text}, {"GradTIE", type text}, {"%Zeef  total", type number}, {"BreKen Vsi", type number}, {"BreKen Vsi total", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Prev Row Difference" = Table.AddColumn(#"Added Index", "Result_", each try
#"Added Index" {[Index] -1 }[BreKen Vsi total]
otherwise
null),
    #"Added Custom" = Table.AddColumn(#"Prev Row Difference", "Result", each try
#"Prev Row Difference" {[Index] +1 }[[Result_]]
otherwise
null),
    #"Expanded Result" = Table.ExpandRecordColumn(#"Added Custom", "Result", {"Result_"}, {"Result_.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Result",{"Index", "Result_"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Result_.1", type number}})
in
    #"Changed Type1"

     

     

     

    let me know if this is an acceptable solution.

     

    for reference: (8) Refer to Previous Row in Power BI - YouTube

     

    Appreciate a thumbs up if this is helpful.

     

     

    • marsclone's avatar
      marsclone
      Helper IV
      3 years ago

      Hi adudani 

      I'm struggling how to implement your solution in my Datamodel.

      This model is bigger then the eight rows i now become?

      Regards

      Marcel

      • adudani's avatar
        adudani
        Memorable Member
        3 years ago

        marsclone ,

         

        What difficulty are you facing?

         

        Are you able to provide a sample pbix removing sensitive information?

         

        Let me know.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi marsclone ,

     

    // All columns are measures in this model.

    You might create a measure like this.

     

    Measure = 
VAR __cur_row1 = SELECTEDVALUE('financials'[Country])
VAR __cur_row2 = SELECTEDVALUE('financials'[Product])
VAR __next_row = CALCULATE(MIN('financials'[Product]),'financials'[Country]=__cur_row1&&'financials'[Product]>__cur_row2)
VAR __next_value = CALCULATE([Sales],'financials'[Country]=__cur_row1&&'financials'[Product]=__next_row)
VAR __result = IF(__next_value<>BLANK(),__next_value - [Sales])
RETURN
__result

     

    Please refer to the sample file.

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • marsclone's avatar
      marsclone
      Helper IV
      3 years ago

      Hi Anonymous 

      I think your solution is the best way to achieve what i want.

      I have only the following problems. I work with power query / power pivot.

      So "selectedvalue" is not available, so i use if(hasonevalue).

      Now i become the error that in a true/false expression only one column can be used.

      Is there a solution for this?

       

      My measure now:

      % per zeef:=

      VAR __cur_row1 = IF(HASONEVALUE(Tussentabel[Nr]);VALUES(Tussentabel[Nr]))
      VAR __cur_row2 = IF(HASONEVALUE(Gradaties[Gradatie]);VALUES(Gradaties[Gradatie]))
      VAR __next_row = CALCULATE(MIN(Gradaties[Gradatie]);Tussentabel[Nr]=__cur_row1&&Gradaties[Gradatie]>__cur_row2)
      VAR __next_value = CALCULATE([Breken VSI totaal];Tussentabel[Nr]=__cur_row1&&Gradaties[Gradatie]=__next_row)
      VAR __result = IF(__next_value<>BLANK();__next_value - [Breken VSI totaal])
      RETURN
      __result

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi marsclone ,

         

        Try this:

        % per zeef:=
VAR __cur_row1 = MAX(Tussentabel[Nr])
VAR __cur_row2 = MAX(Gradaties[Gradatie])
VAR __next_row = CALCULATE(MIN(Gradaties[Gradatie]);Tussentabel[Nr]=__cur_row1&&Gradaties[Gradatie]>__cur_row2)
VAR __next_value = CALCULATE([Breken VSI totaal];Tussentabel[Nr]=__cur_row1&&Gradaties[Gradatie]=__next_row)
VAR __result = IF(__next_value<>BLANK();__next_value - [Breken VSI totaal])
RETURN
__result

         

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum