Forum Discussion
Calculate trend value using linear regression
Hi All,
I have a requirement to calculate trend(which will be shown as Up and Down arrow) using linear regression based on filters selected in the report including date range, region and other filters.
BBelow are the examples.
Eample 1:
| Date | Total |
| 3/26/2020 | 10 |
| 3/27/2020 | 23 |
| 3/28/2020 | 30 |
| 3/29/2020 | 60 |
| 3/30/2020 | 30 |
| 3/31/2020 | 240 |
| 4/1/2020 | 80 |
As per Linear regression in excel, Trend is positive and going up. Hence, Trend which I have to show will be an up arrow.
Example 2:
| Date | Total |
| 3/26/2020 | 40 |
| 3/27/2020 | 44 |
| 3/28/2020 | 34 |
| 3/29/2020 | 50 |
| 3/30/2020 | 40 |
| 3/31/2020 | 22 |
| 4/1/2020 | 21 |
Since the trend is going down, I have to show final trend as down arrow.
Thanks in advance!
5 Replies
Thanks greg! I tried with the DAX metioned in the link and got below result. Selecting table visual gives me same count as actual.
As per my requirement, I have to show if it is positive trend or negative using below arrows. How would I achieve that?
Well, you will have to find the slope of the line. Slope of line is the equation:
Slope = (Y2 - Y1) / (X2 - X1)
I would use the MIN and MAX of Y's and X's to compute the slope.
Hi guys,
The article of Daniil is excellent.
Just fyi, there isin 2023 the new function LINESTX which will make here our life easier. 🙂
Documentation from MSFT: https://learn.microsoft.com/en-us/dax/linestx-function-dax
Video from SQLBI https://www.youtube.com/watch?v=EmsR8lc7w78&t=1s
Have fun.
G.
