Forum Discussion
Creating custom column or measure
This code adds a calculated column
Trend =
VAR _tbl =
FILTER (
SUMMARIZE (
'DataTable',
'DataTable'[Name],
'DataTable'[Price],
'DataTable'[Updated_At]
),
'DataTable'[Name] = EARLIER ( 'DataTable'[Name] )
)
VAR _dif =
MAXX (
INDEX ( -1, _tbl, ORDERBY ( 'DataTable'[Updated_At] ) ),
'DataTable'[Price]
)
- MAXX (
INDEX ( -2, _tbl, ORDERBY ( 'DataTable'[Updated_At] ) ),
'DataTable'[Price]
)
RETURN
IF (
ISBLANK ( 'DataTable'[parent_id] ),
SWITCH (
TRUE (),
OR ( COUNTROWS ( _tbl ) = 1, _dif = 0 ), "Uncahged",
_dif > 0, "Increasing",
"Decreasing"
)
)
thanks, this is working, can you provide me M code also so i can make the custom column using query editor?
HI, zebra here goes M
let Source = your_product_table, values = {"decreasing", "unchanged", "increasing"}, f = (tbl as table) as table => [prices = List.LastN(tbl[Price], 2), comp = Value.Compare(prices{1}? ?? prices{0}, prices{0}) + 1, add = Table.AddColumn(tbl, "Trend", (x) => if x[parent_id] = null then values{comp} else null)][add], group = Table.Group(Source, {"Name"}, {{"all", each f(Table.Sort(_, "Updated_At"))}}), expand = Table.ExpandTableColumn(group, "all", {"Id", "parent_id", "Price", "Updated_At", "Trend"}) in expandthe above M code gives me the following error
Expression.Error: A cyclic reference was encountered during evaluation.
