Help with Linear Trend line ?

[ashmitp869]

Hi,

Help me to get the trend line like below .

I have used

LINESTX but I am getting the result below.

I have attached my sample pbix file.

https://github.com/suvechha/samplepbi/blob/main/sampleTrend.pbix

Thanks

[v-tejrama]

Hi @ashmitp869 ,

Thank you @Ritaf1983  and @parry2k for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you.

[Ritaf1983]

Hi @ashmitp869 

From what I see, the trend lines are much lower than the actual values, which indicates a calculation issue.
This usually happens because the formula removes filters from Month-Year, but columns that are indirectly related to that field (like a column used for sorting) still participate in the query behind the scenes.
So even if the visual doesn’t directly use the month column, it still filters the calculation context.
That’s why when you want to manipulate context involving Month-Year, it’s better either to include the related columns as well, or to operate at the Calendar table level — for example, like in my formula below:

Trend for Goals Scored =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'Calendar' ),
"KnownX", 'Calendar'[yy-mm],
"KnownY", [Sales]
),
NOT ( ISBLANK ( [KnownX] ) )
&& NOT ( ISBLANK ( [KnownY] ) )
)
VAR SlopeIntercept =
LINESTX ( Known, [KnownY], [KnownX] )
VAR Slope =
SELECTCOLUMNS ( SlopeIntercept, "Slope", [Slope1] )
VAR Intercept =
SELECTCOLUMNS ( SlopeIntercept, "Intercept", [Intercept] )
RETURN
SUMX (
DISTINCT ( 'Calendar'[yy-mm] ),
Intercept + Slope * 'Calendar'[yy-mm]
)

The pbix with the example is attachd

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[ashmitp869]

Hi @Ritaf1983 

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.

[v-tejrama]

Hi @ashmitp869 ,

We kindly suggest raising a support ticket with the partners group: Power BI Partners | Microsoft Power Platform as they will be able to investigate the matter in detail and provide deeper assistance. I also tried several workarounds on my side, but I was unable to change the measure in the PBIX file you provided, so involving the supporting partners group would be the best next step.

Thank you.

[v-tejrama]

Hi @ashmitp869 ,

I am following up to check if you have been able to contact Partners Group. Please let me know if you require any support from me to proceed.

Thank you.

[v-tejrama]

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.

[ashmitp869]

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 

@Ritaf1983 

@v-tejrama still needs help as not getting the correct result.

[Syndicate_Admin]

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

[ashmitp869]

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 Trend

Infringements Trend

[ashmitp869]

When I check the data in table format.

Your Trends line is different than mine, even the data are same.
Suggested 

My report data

What is the reason for this ? What else do I need to change ?

[Ritaf1983]

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

[ashmitp869]

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

[Ritaf1983]

@Syndicate_Admin 
This is my solution why it appears with your name?

[parry2k]

@ashmitp869 maybe try to change currentx value to this:

VAR CurrentX = RANKX(ALL('Date'[Year-Month Code], 'Date'[MonthYear]), CALCULATE( MAX('Date'[Year-Month Code])), , DESC)