I need output like this by using measure ( calculate previous row difference)

[Karthikgayathri]

[rubayatyasmin · Accepted solution]

Here is the working DAX

TimePlayed_Difference =

    VAR CurrentIndex =MAX( 'Table'[Index])

    VAR CurrentTimePlayed = 'Table'[total]

    VAR PreviousTimePlayed =

        CALCULATE(

            MAX('Table'[TimePlayed]),

            FILTER(

                ALL('Table'),

                'Table'[Index] = CurrentIndex - 1

            )

        )

    RETURN

        IF(

            CurrentIndex = 1,

            CurrentTimePlayed,

            CurrentTimePlayed - PreviousTimePlayed

        )

Did I help? If yes, hit 👍 and accept this answer as a solution. 

Thanks

[rubayatyasmin · Accepted solution]

It will change obviously. check the values if it matched with your desired output or not. I can clearly see that it matched. -520 because we do not have row 1 value. Do we? value started from row 2. Mathematically the answer is right.  

[rubayatyasmin]

I think you are missing something. Here is the solution file.  

[Karthikgayathri]

File showing deleted 

[rubayatyasmin]

Here is the file. On this website, the file is deleted after you download it. 

[Karthikgayathri]

@rubayatyasmin 

2 table (A,B )

A - 3 rows (1,3,1 )

B - 4 rows (1,1,5,1)

 I need output for inner join and left outer join bro

[rubayatyasmin · Accepted solution]

For, Inner join - you will get 1 output (inner join takes only matching rows)\

for left join - you will get 2 output. (all from left, matching from right)

see sql joins

[rubayatyasmin]

Hi, @Karthikgayathri 

you can create a custom column. I created sample values like yours. see the screenshot below. 

I think this is what you wanted. 

M code is: if [Index] = 1 then [TimePlayed] else [TimePlayed] - #"Added Index"{[Index]-2}[TimePlayed]

in PBI report:

do not forget to sort the category column by Index. 

Did this post help? If yes, hit 👍 and accept this answer as a solution. 

Thanks

[Karthikgayathri]

Bro I already used this method in power query. I need another method by using variables or  by using measure or any other performance best method 

[rubayatyasmin · Accepted solution]

Here is the working DAX

TimePlayed_Difference =

    VAR CurrentIndex =MAX( 'Table'[Index])

    VAR CurrentTimePlayed = 'Table'[total]

    VAR PreviousTimePlayed =

        CALCULATE(

            MAX('Table'[TimePlayed]),

            FILTER(

                ALL('Table'),

                'Table'[Index] = CurrentIndex - 1

            )

        )

    RETURN

        IF(

            CurrentIndex = 1,

            CurrentTimePlayed,

            CurrentTimePlayed - PreviousTimePlayed

        )

Did I help? If yes, hit 👍 and accept this answer as a solution. 

Thanks

[Karthikgayathri]

Ok output crt bro but i need 2 nd column previous row value and third column only I need difference ( like below images). In your output both the column denotes difference only. I m struggling mainly to get 2 nd column  as previous row value. Without bringing index column any other way by using earlier function ? 

 

[rubayatyasmin]

Here is the second DAX:

TimePlayed_Remaining =

    VAR CurrentRowID = MAX('Table'[Index]) // Replace with your unique row identifier

    VAR CurrentTimePlayed = CALCULATE(MAX('Table'[TimePlayed]), 'Table'[Index] = CurrentRowID)

    RETURN

       IF(CurrentRowID = 1, BLANK(), CurrentTimePlayed-[TimePlayed_Difference])

[Karthikgayathri]

• 

sry for that blur image because my phone camera not working properly.above  image total mismatch ( both prev value and measure  interchange)as well instead of showing 100 in prev value showing 0  wht is the reason . In time played difference column, u mentioned table[total] . total denotes wht ?

[rubayatyasmin]

Total is a measure for the sum(timePlayed)

[Karthikgayathri]

See total value in output interchanged bro (-520,580)

[rubayatyasmin · Accepted solution]

It will change obviously. check the values if it matched with your desired output or not. I can clearly see that it matched. -520 because we do not have row 1 value. Do we? value started from row 2. Mathematically the answer is right.