Calculate difference between two rows

[supermulder]

 Hi there,

i have a simple table with 2 columns

date / value

2016-02-10 / 1000

2016-02-11 / 1200

2016-02-12 / 1500

What i need is a dynamic column "diff" that considers the difference in value between each row -

date / value / diff

2016-02-10 / 1000 / 0

2016-02-11 / 1200 / 200

2016-02-12 / 1500 / 300

What i have tried is:

diff  ='Table'[value]) - CALCULATE(COUNT('Table'[value]);FILTER('Table';'Table'[date] = DATEVALUE('Table'[date] - 1)))

With this formula i get no result - do you have any idea what to do ?

Thank you!

[fbrossard]

Hi @supermulder,

It depends on your needs, if you measure is  "context filter" depend, you should you @MattAllington DAX solution.

If not, you can also precalc the diff at leaf level with Power Queery by implementing running total techniques with Power Query :

1. Create you data source query (for example SampleDataSource), order you data by "date" column, add a first index starting by 1, add a second index called previous index starting by 0 and modify your query in order to create only connection . You should have this M code :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNM1MNI1NFDSUTI0MDBQitVBEjUEiRqhixqBRE1BorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Change type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Order rows" = Table.Sort(#"Change type",{{"Date", Order.Ascending}}),
    #"Add index started by 1" = Table.AddIndexColumn(#"Order rows", "Index", 1, 1),
    #"Add index started by 0" = Table.AddIndexColumn(#"Add index started by 1", "Previous Index", 0, 1)
in
    #"Add index started by 0"

2. Create a new query by Referecing the first one (for example Sample). Merge the 2 queries with left join by using Sample.Previous Index = SampleDataSource.Index. Expand the referenced table to get the value, calc the diff and remove all temp columns. Finally, you should have this M Code :

let
    Source = SampleDataSource,
    #"Merge queries" = Table.NestedJoin(Source,{"Previous Index"},SampleDataSource,{"Index"},"Previous",JoinKind.LeftOuter),
    #"Previous expdanded" = Table.ExpandTableColumn(#"Merge queries", "Previous", {"Value"}, {"Previous.Value"}),
    #"Add custom column Diff" = Table.AddColumn(#"Previous expdanded", "Diff", each [Value] - [Previous.Value]),
    #"Delete temp columns" = Table.RemoveColumns(#"Add custom column Diff",{"Index", "Previous Index", "Previous.Value"})
in
    #"Delete temp columns"

Just use diff column on your visuals.

[MattAllington]

Can I ask why you need this column?  It is not something that I would consider normal DAX. What are you trying to achieve in the end?

[supermulder]

In value i have always the cumulated value over all - it´s perfect for display in a line widget for example - but what i want is a quick overview about the absolute performance over a given period to display.

So at the moment i have

day 1: 20000

day 2: 30000

day 3: 40000

What i want additionaly is the growth between days:

day 1: 0

day 2: 10000

day 3: 10000

At the end i want to display this values in a bar-widget for the last 7 days and more cumulated for month and quarter reports - i hope you unterstand what my intention is.

[MattAllington]

So do you always only have 1 record per day? Either way it doesn't really matter   I would connect the data table to a calendar table, make a working day ID column in the calendar table (start at 1 and increment by 1 for each day).  Null or zero for a non working day.

eg

0 = Sunday 

1

2

3

4

5

0

0

6

7

8

9

10

0

0

etc

 Then write measures (not a calc column) like this

Today=sum(table[value])

Previous working day =calculate(sum(table[value]),filter(all(calendar),calendar[ID] =max(calendar[ID])-1))

chg vs prior working day = [Today] - [Previous a Working Day]

set a matrix, put the calendar[date] on rows and then add the three measures. 

Edit: you may want to repeat the ID number for the last working day instead of blanks. 

0

1

2

3

4

5

5

5

6

7

8

9

10

10

10

etc