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

      • Ritaf1983's avatar
        Ritaf1983
        Super User
        9 months ago

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