Forum Discussion
Help with Linear Trend line ?
Hi ashmitp869 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
I have used your Trend for Goals Scored, but still getting same result.
Will you have a look on the sample file.
https://github.com/suvechha/samplepbi/blob/main/sampleTrend.pbix
The Excel provide Linear Trendline like
@v-tejrama still needs help as not getting the correct result.
Hi @Syndicate_Admin
Try These formulas.
They include monthly aggregation before the trend calculationInfringements Trend =
VAR Months =
-- One row per month (MonthYear + numeric month order)
SUMMARIZE (
ALLSELECTED ( 'Date' ),
'Date'[Year-Month Order], -- numeric chronological key (YYYYMM)
'Date'[MonthYear] -- display label
)
VAR Known =
-- Add X and Y for regression
ADDCOLUMNS (
Months,
"KnownX",
RANKX (
Months,
'Date'[Year-Month Order], -- sorting by monthly numeric key
,
ASC
),
"KnownY",
CALCULATE ( [Infringements] ) -- monthly aggregated value
)
VAR KnownFiltered =
-- Remove empty months
FILTER ( Known, NOT ISBLANK ( [KnownY] ) )
VAR SlopeIntercept =
-- Linear regression over monthly points
LINESTX ( KnownFiltered, [KnownY], [KnownX] )
VAR Slope =
-- Regression slope (scalar)
MAXX ( SlopeIntercept, [Slope1] )
VAR Intercept =
-- Regression intercept (scalar)
MAXX ( SlopeIntercept, [Intercept] )
VAR CurrentMonthOrder =
-- The month currently in row context of the visual
SELECTEDVALUE ( 'Date'[Year-Month Order] )
VAR CurrentX =
-- Lookup the X-value (rank) for the current month
MAXX (
FILTER ( Known, 'Date'[Year-Month Order] = CurrentMonthOrder ),
[KnownX]
)
RETURN
IF (
ISBLANK ( CurrentMonthOrder ),
BLANK (),
Intercept + Slope * CurrentX -- trend value for the current month
)DL Validation Trend =
VAR Months =
-- One row per month (MonthYear + numeric month order)
SUMMARIZE (
ALLSELECTED ( 'Date' ),
'Date'[Year-Month Order], -- numeric chronological key (YYYYMM)
'Date'[MonthYear] -- display label
)
VAR Known =
-- Add X and Y for regression
ADDCOLUMNS (
Months,
"KnownX",
RANKX (
Months,
'Date'[Year-Month Order], -- sorting by monthly numeric key
,
ASC
),
"KnownY",
CALCULATE ( [Drivers Licence Validation] ) -- monthly aggregated value
)
VAR KnownFiltered =
-- Remove empty months
FILTER ( Known, NOT ISBLANK ( [KnownY] ) )
VAR SlopeIntercept =
-- Linear regression over monthly points
LINESTX ( KnownFiltered, [KnownY], [KnownX] )
VAR Slope =
-- Regression slope (scalar)
MAXX ( SlopeIntercept, [Slope1] )
VAR Intercept =
-- Regression intercept (scalar)
MAXX ( SlopeIntercept, [Intercept] )
VAR CurrentMonthOrder =
-- The month currently in row context of the visual
SELECTEDVALUE ( 'Date'[Year-Month Order] )
VAR CurrentX =
-- Lookup the X-value (rank) for the current month
MAXX (
FILTER ( Known, 'Date'[Year-Month Order] = CurrentMonthOrder ),
[KnownX]
)
RETURN
IF (
ISBLANK ( CurrentMonthOrder ),
BLANK (),
Intercept + Slope * CurrentX -- trend value for the current month
)Ther updated pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Syndicate_Admin
This is my solution why it appears with your name?Hi Ritaf1983 Syndicate_Admin v-tejrama
Thanks for your solution.
But when I am using this dax expression. I am getting the trendline like below. Not as expected what you shared.
Why this is different when I implement in my pbix file ?DL Validation TrendInfringements TrendWhen I check the data in table format.
Your Trends line is different than mine, even the data are same.
SuggestedMy report data
What is the reason for this ? What else do I need to change ?
