Forum Discussion

MarkSL's avatar
MarkSL
Icon for Helper V rankHelper V
8 years ago
Solved

DAX to create a Trend line?

Hi,

 

I really want to add a Trend Line to a Line and stacked column chart, however the Analytics options aren't availabe for this type of visual.  Is it possible to create a Trend using DAX , the same as using TREND function in Excel?

 

I can add one to a standard line chart:

 

Trend on Line Chart

 

 

 

 

 

 

 

 

 

 

 

 

But I would really like it on my Line and Stacked column chart:

 

Line and Stacked column chart

The formula would need to take into account any slicers which change the date range.

 

Any thoughts / tips greatly appreciated!

 

Mark

 

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hi MarkSL

     

    Just tested it out and the issue is with ALLSELECTED ( 'DateTable'[Date] ). It doesn't work as intended when you filter on a column other that Date, such as Month.

     

    One possible fix is the change in red below.

    I have restated your entire code for completeness.

     

    That should work (tested a mock-up model at my end) but let me know if it doesn't

     

    Oh, by the way, there is a "Combine Series" setting for trendlines that determines whether each series gets its own trend line.

     

    Regards,

    Owen

     

    Estimated Sales =
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            CALCULATETABLE ( VALUES ( 'DateTable'[Date] ), ALLSELECTED ('DateTable') ),
            "Known[X]", 'DateTable'[Date],
            "Known[Y]", [SalesDaily2]
        ),
        AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
    )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X ^ 2
    )
VAR Intercept = Average_Y
    - Slope * Average_X
RETURN
    SUMX ( DISTINCT ( 'DateTable'[Date] ),
    Intercept + Slope * 'DateTable'[Date]
)

21 Replies

    • MarkSL's avatar
      MarkSL
      Icon for Helper V rankHelper V
      8 years ago

      Thanks both for the quick replies.  

       

      Owen, I tried the second Dax example from your first link (Other ways to use the pattern) and I seem to have created my trend line as desired!  However, it has raised another question about the provided Trend Line provided in Analytics...

       

      I first added my new calculated trend line (yellow) to the my Line chart to check that it matched the previously applied automatic trend line (dashed), but found that it sat above it and not on it:

       

       

      So I initially thought something was not quite right.  However, when I removed the Budget line (red) from the chart, the automatic Trend line moved to sit exactly on my created trend line:

       

       

      On looking at the automatic Trend line provided by Analytics, it doesn't ask which measure (line) on the chart you want the trend to be based on, which seems a bit odd to me?  I assume that it must be based on both measures, Sales and Budget?

       

      Anyway, I don't require the Line chart as my trend line is now showing as desired on my Line and Bar chart which is what I really wanted:

       

       

      I would be interested to hear your thoughts on the automatic Trend line moving though?

       

      Thanks

       

      Mark

      • MarkSL's avatar
        MarkSL
        Icon for Helper V rankHelper V
        8 years ago

        Ah, I may have spoken to soon of success...

         

        It appears that when I filter on month, to reduce the dataset, my calculated trend does not match the automatic trend.  However, if I filter on another field, such as customer, the two lines do still match??  I am very new to DAX and so am not sure why this is happening?

         

        Here is the code for the calculated trend:

         

        Estimated Sales = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'DateTable'[Date] ),
            "Known[X]", 'DateTable'[Date],
            "Known[Y]", [SalesDaily2]
        ),
        AND (
            NOT ( ISBLANK ( Known[X] ) ),
            NOT ( ISBLANK ( Known[Y] ) )
        )
    )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X ^ 2
    )
VAR Intercept =
    Average_Y - Slope * Average_X
RETURN
    SUMX (
        DISTINCT ( 'DateTable'[Date] ),
        Intercept + Slope * 'DateTable'[Date]
    )

         

        Thanks again.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi all in my case I wanted to get it done in a different context than date. So below you'll find the lines that can be adjusted to any context: Just by changing the measure and the context

    TrendIN(SimpleLinearRegression) =
    //external measure "imonthsinrow=CALCULATE(DISTINCTCOUNT(tMasterCalendar[YearMonth]);FILTER(ALLSELECTED(tMasterCalendar);tMasterCalendar[Date]<=MAX(tMasterCalendar[Date])))"

    Var i=CALCULATE(DISTINCTCOUNT(tMasterCalendar[YearMonth]);ALLSELECTED(tMasterCalendar))
    //Calaculates count of month in selected period

    Var avgx=CALCULATE(AVERAGEX(VALUES(tMasterCalendar[YearMonth]);[iMonthsinrow]);ALLSELECTED(tMasterCalendar))
    //Calaculates avg i value on selected period

    Var avgy=CALCULATE(AVERAGEX(VALUES(tMasterCalendar[YearMonth]);[IN]);ALLSELECTED(tMasterCalendar))
    // Calaculates avg IN value on selected period

    Var Slope=DIVIDE(
    CALCULATE(SUMX(VALUES(tMasterCalendar[YearMonth]);([iMonthsinrow]-avgx)*([IN]-avgy));ALLSELECTED(tMasterCalendar));
    CALCULATE(SUMX(VALUES(tMasterCalendar[YearMonth]);([iMonthsinrow]-avgx)^2);ALLSELECTED(tMasterCalendar)))
    //Calculating a in y=ax+b

    Var b=avgy-Slope*avgx //Calculating b in y=ax+b

    Return
    b+[iMonthsinrow]*Slope