Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
marsclone
Helper IV
Helper IV

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.

 

marsclone_0-1675417436096.png

 

7 REPLIES 7
v-cgao-msft
Community Support
Community Support

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

 

vcgaomsft_0-1675663375279.png

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

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

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

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
Super User
Super User

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.

adudani_1-1675437035938.png

 

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

 

Appreciate a thumbs up if this is helpful.

 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

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

@marsclone ,

 

What difficulty are you facing?

 

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

 

Let me know.

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.