Difference between two rows

[Anonymous]

Hi Team, 

i have this function to calculate difference between rows:

QF Trade Ups2 =
VAR myindex=
selectedvalue('QF Core Pricing'[Fare Index - Text])
return
[MIN AIF QF]
-CALCULATE([MIN AIF QF],FILTER(ALL('QF Core Pricing'),VALUE('QF Core Pricing'[Fare Index - Text])=value(myindex)-1)
)

which leads to something weird:

Any idea why

[Greg_Deckler]

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Typically you would need to use EARLIER to achieve what you are going for. 

[Anonymous]

Hi, 

why should I use Earlier? I don't have a time comparison to achieve.

Sorry too long, don't have time. 

[Greg_Deckler]

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

[Anonymous]

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 😄

[v-frfei-msft]

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

[Anonymous]

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?

[v-frfei-msft]

Hi @Anonymous,

To create a calculated column as below.

Column = var index = CALCULATE(MAX(Table1[Index ]))
return
IF(index=1,0,Table1[AIF] -CALCULATE(SUM(Table1[AIF]),FILTER(Table1,Table1[Index ]=index-1)))

Regards,

Frank

[Anonymous]

No Luck again:  

Trade  Up = var index=Calculate(MAX('QF Core Pricing'[Fare Index]))
return
if(index=1,0,'QF Core Pricing'[AIF]-calculate(sum('QF Core Pricing'[AIF]),Filter('QF Core Pricing','QF Core Pricing'[Fare Index]=index-1)))

could it be that it needs a filter for the market and restriction columns in order to work?

[v-frfei-msft]

Hi @Anonymous,

Is that possbile to share your pbix to me? You can upload the file to dropbox and share the link to me.

Regards,

Frank

[Anonymous]

Hi Frank, 

use this one, where sensible data has been removed:

https://www.dropbox.com/s/r7pdon0duhtm55s/Fare%20Grids%20-%20msft.pbix?dl=0

let me know if doesn't work.

cheers

Alberto

[Greg_Deckler]

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.

[Anonymous]

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)