The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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!
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.
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.
Tried tweaking the code as per you suggestion, but it didn't help. May be I am doing something wrong.
In the DAX, instead of below
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
157 | |
137 | |
132 | |
81 | |
61 |