Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Deemo
Helper II
Helper II

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: 

DateTotal
3/26/202010
3/27/202023
3/28/202030
3/29/202060
3/30/202030
3/31/2020240
4/1/202080

 

As per Linear regression in excel, Trend is positive and going up. Hence, Trend which I have to show will be an up arrow.

 
 

Example1.JPG

 

Example 2:

DateTotal
3/26/202040
3/27/202044
3/28/202034
3/29/202050
3/30/202040
3/31/202022
4/1/202021

 

Example2.JPG

Since the trend is going down, I have to show final trend as down arrow.

 

Thanks in advance!

 

 

5 REPLIES 5
Ghuiles
Advocate IV
Advocate IV

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.

 

 

Greg_Deckler
Super User
Super User

https://community.powerbi.com/t5/Quick-Measures-Gallery/Simple-Linear-Regression/td-p/247439

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks greg! I tried with the DAX metioned in the link and got below result. Selecting table visual gives me same count as actual.

Estimated.JPG

As per my requirement, I have to show if it is positive trend or negative using below arrows. How would I achieve that?

Down.JPGUp.JPG

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

RETURN
SUMX (
DISTINCT ( 'Date'[Date] ),
Intercept + Slope * 'Date'[Date]
)
tried RETURN Slope, It gives positive/ negative values as per the trend. Although it works, but not sure if it would be correct to show up/down arrows as per these values
Negative.JPGPositive.JPG

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.