Calculate next row minus current row?

[marsclone]

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.

[v-cgao-msft]

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]

Hi @v-cgao-msft 

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

[v-cgao-msft]

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

[marsclone]

Hi @v-cgao-msft 

I still become the same error. Maybe you can take a look in this model.

I hope you will find a solution.

Thank you in advance!

Test 

[adudani]

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]

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]

@marsclone ,

What difficulty are you facing?

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

Let me know.