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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors