Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
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?
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |