Forum Discussion
Difference between two rows
Hi,
why should I use Earlier? I don't have a time comparison to achieve.
Sorry too long, don't have time.
Read what you were doing too quickly. So, I assume that this is a measure. I would try MAX instead of SELECTEDVALUE. SELECTEDVALUE is typically used when dealing with a slicer. MAX of your Fare Index in the context of a row should return the value of the Fare Index in that row. Not sure what SELECTEDVALUE would return for something like that.
Sample source data would allow me to try different things out and be specific with a solution. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
either SELECTEDVALUE or MAX just comes to the same situation where there is data make no sense:
I'm sorry i don't have time to go the whole answer and to give to you an example:
Index AIF Tradeup 1 100 0 2 250 150 3 300 50 4 450 150 i just need the difference between rows :D
OK, I created both a column using EARLIER and a measure.
This is the query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MFCK1YlWMgKyjUwhbGMg2xgqbgJkm4DEYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, AIF = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"AIF", Int64.Type}}) in #"Changed Type"This is the column:
Column = VAR __previous = MAXX(FILTER(ALL(Table1),[Index]=EARLIER([Index])-1),[AIF]) RETURN IF(ISBLANK(__previous),0,[AIF]-__previous)
And this is the measure:
Measure = VAR __index = MAX([Index]) VAR __previous = MAXX(FILTER(ALL(Table1),[Index]=__index-1),[AIF]) RETURN IF(ISBLANK(__previous),0,MAX([AIF])-__previous)
Also attached.
Sorry Man,
no luck with this 2 solutions:
QF Trade Up = VAR __index = MAX([Fare Index]) VAR __previous = MAXX(FILTER(ALL('QF Core Pricing'),[Fare Index]=__index-1),[AIF]) RETURN IF(ISBLANK(__previous),0,MAX([AIF])-__previous)Trade Up column = VAR __previous = MAXX(FILTER(ALL('QF Core Pricing'),[Fare Index]=EARLIER([Fare Index])-1),[AIF]) RETURN IF(ISBLANK(__previous),0,[AIF]-__previous)
Hi Anonymous,
We can create a measure to work on it.
Measure = VAR index = MAX ( Table1[Index ] ) RETURN IF ( index = 1, 0, CALCULATE ( SUM ( Table1[AIF] ) ) - CALCULATE ( SUM ( Table1[AIF] ), FILTER ( ALL ( Table1 ), Table1[Index ] = index - 1 ) ) )Also please find the pbix attached.
Regards,
Frank
Hi Frank,
mmm I believe we have to include more variables. This is what I got
Trade Up = VAR index = max('QF Core Pricing'[Fare Index]) return if( index = 1, 0, calculate(sum('QF Core Pricing'[AIF]))- calculate(sum('QF Core Pricing'[AIF]),FILTER(ALL('QF Core Pricing'),'QF Core Pricing'[Fare Index]= index-1) ) )Columns Market and restriction are relevant. It looks like this measure is just doing it for the whole query.
What if we make it as a column?
