Creating custom column or measure

[zebra]

Hey All,

I am having hard time in creating custom column/measure according to following scnario. Suppose I have product table as follows

Id	parent_id	Name	Price	Updated_At
1	null	p1	100	13/10/2023
2	1	p1	150	14/10/2023
3	null	p2	200	15/10/2023
4	null	p3	100	10/10/2023
5	4	p3	50	11/10/2023
6	4	p3	70	12/10/2023

The Product table has products with its various revision according to price change. I want to make another column thats shows the trend.  The final output would be as follows.

Id	parent_id	Name	Price	Updated_At	Trend
1	null	p1	100	13/10/2023	increasing
2	1	p1	150	14/10/2023
3	null	p2	200	15/10/2023	unchanged
4	null	p3	100	10/10/2023	increasing
5	4	p3	50	11/10/2023
6	4	p3	70	12/10/2023

Here you can see that for p1, we have its price changed in 2nd row so the trend is increasing. for p2,  there are no revisions so trens is unchanged and for p3, we have two variations and here we need to compare last two and according to it, price is increasing so we have increasing trend. 

Would anyone help me in calculating these using custom columns?

Thanks 

zeebee

[FreemanZ]

hi @zebra ,

you may try to plot a table visual with the name column and a measure like:

measure = 
VAR _table = FILTER(data,data[Name]=MAX(data[Name]))
VAR lastprice =
MAXX(
    TOPN(1,_table, data[Updated_At]),
    data[Price]
)
VAR llastprice=
MAXX(
    EXCEPT(
        TOPN(2, _table, data[Updated_At]),
        TOPN(1, _table, data[Updated_At])
    ),      
    data[Price]
)
RETURN
SWITCH(
    TRUE(),
    llastprice = BLANK() || lastprice=llastprice, "Unchanged",
    lastprice>llastprice, "Increasing",
    "decreasing"
)

it worked like:

[zebra]

This is not the correct solution as it gives me the following output when I added the data as follows

I need a measure/column that gives the trend in the orignal data format

[FreemanZ]

hi @zebra ,

if you need to bring all the columns into the visual, tweak the code like:

measure = 
VAR _table = FILTER(ALL(data),data[Name]=MAX(data[Name]))
VAR lastprice =
MAXX(
    TOPN(1,_table, data[Updated_At]),
    data[Price]
)
VAR llastprice=
MAXX(
    EXCEPT(
        TOPN(2, _table, data[Updated_At]),
        TOPN(1, _table, data[Updated_At])
    ),      
    data[Price]
)
RETURN
SWITCH(
    TRUE(),
    llastprice = BLANK() || lastprice=llastprice, "Unchanged",
    lastprice>llastprice, "Increasing",
    "decreasing"
)

[wdx223_Daniel]

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"
        )
    )

[zebra]

@wdx223_Daniel , The above solution works however It is not working in the following scenario. I have another type of input table as follows. In this, I need to calculate trend based on step, there are finite number of steps lets say step1, step2, step3 and step 4.  and if step is changing, it is either increasing or decreasing and if there is only one row then it is unchanged. 

Id	parent_id	other columns	step	Updated_At
1	null	…	step 1	13/10/2023
2	1	…	step 2	14/10/2023
3	null	…	step 1	15/10/2023
4	null	…	step  1	10/10/2023
5	4	…	step 2	11/10/2023
6	4	…	step 1	12/10/2023

and I want to have trend according to steps as follows

Id	parent_id	other columns	step	Updated_At	Trend
1	null	…	step 1	13/10/2023	step increasing
2	1	…	step 2	14/10/2023
3	null	…	step 1	15/10/2023	unchanged
4	null	…	step  1	10/10/2023
5	4	…	step 2	11/10/2023	step increasing
6	4	…	step 1	12/10/2023

Moroever, please provide the explanation of the code as I am new to this tool and unable to understand how you are calculating. I try to use dax studio to see the output of the intermediate steps however dax studio throwing errors as shown below.

So it will be great if you explain the steps and also let me know how i can see the output of intermediate steps?

thanks

zebee

[zebra]

thanks, this is working, can you provide me M code also so i can make the custom column using query editor?

[AlienSx]

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
    expand

[zebra]

the above M code gives me the following error

Expression.Error: A cyclic reference was encountered during evaluation.

[AlienSx]

@zebra probably because you are trying to add new column with this code. Don't do that. Imagine you have your product table in PQ as separate query named "your_product_table". Then create a blank query with my code and see what happens. Check this video created by @ImkeF especially for you. 

[AlienSx]

sorry, @zebra , here is video