Forum Discussion

ashmitp869's avatar
ashmitp869
Responsive Resident
9 months ago

Help with Linear Trend line ?

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/samp...
Need Help
v-tejrama's avatar
v-tejrama
Community Support
9 months ago

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's avatar
    v-tejrama
    Community Support
    9 months ago

    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.

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Administrator
        9 months ago

        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

  • v-tejrama's avatar
    v-tejrama
    Community Support
    9 months ago

    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.