Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
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
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!
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.
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
What difficulty are you facing?
Are you able to provide a sample pbix removing sensitive information?
Let me know.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
35 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
44 | |
24 | |
16 | |
14 | |
14 |