Forum Discussion
Help with Linear Trend line ?
Hi @Syndicate_Admin
Try These formulas.
They include monthly aggregation before the trend calculation
Infringements 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
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 ?
When 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 ?
Hi ashmitp869
If the result is not identical, it means you are not working one-to-one as I instructed.
I attached your PBIX with the updates from the previous solution and am attaching it again.
Aside from a strong recommendation to download it and follow up on the changes I made, unfortunately I don’t have much more to offer on this matter.If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi Ritaf1983
I have done as you instructed.
Will you please have a look on the pbix file .
https://github.com/suvechha/samplepbi/blob/main/sampleTrend%20with%20data.pbix
