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