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 ...
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]
)
matthewtay's avatar
matthewtay
Regular Visitor
3 years ago

THANK YOU SO MUCH! ABSOLUTE LIFE SAVER! IT WORKED!

 

I think some how the issue was that I used the submission date from the original data set even though I had created a new table using the "CALENDARAUTO()" function and linked the submission date to the calendarauto date.

 

Just one last issue, is there anyway I can restrict the data to my actual data set i.e., my data set has data from 1987 to 2023. Currently the trend line is plotted from year 1900 to 3000. While I could manually filter the date range from 1987-2023, just wondering if I could get it to automatically plot between those years where I actually have data.

 

This is also getting a bit ambitious, is there any way I could get the trend line to auto adjust based on the date range selected via the "Zoom slider" for the x-axis (date range)? Currently the trend line is fixed based on the calculation from all the data I have regardless of how I filter the date range on the visualisation.

 

Anyway thanks so much for your help once again!! 

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
3 years ago

matthewtay  you're welcome!

On your questions:

  1. I would recommend adjusting the Trend Line measure to hide values that are outside the date range present in the data table. One way of doing this is shown below, by comparing the global min/max dates in the data table with the filtered min/max dates.
  2. The trend calculation line itself should already adjust based on the overall filter context of the visual due to the use of ALLSELECTED. I just tweaked my measures to use ALLSELECTED without arguments, as this should ensure that all filters at the "overall" visual level are retained. When changing the date range, the trend line appears to adjust as you would expect. Does this work for you?

Updated PBIX attached.

Trend line = 
VAR DataMinDate =
    CALCULATE ( MIN ( data[SubmissionDate] ), REMOVEFILTERS () )
VAR DataMaxDate =
    CALCULATE ( MAX ( data[SubmissionDate] ), REMOVEFILTERS () )
VAR FilteredMinDate = MIN ( 'Date'[Date] )
VAR FilteredMaxDate = MAX ( 'Date'[Date] )
VAR Result =
    IF (
        AND ( FilteredMinDate <= DataMaxDate, FilteredMaxDate >= DataMinDate ),
        VAR Known =
            FILTER (
                SELECTCOLUMNS (
                    CALCULATETABLE (
                        SUMMARIZE ( 'data', 'Date'[Date] ),
                        ALLSELECTED ( )
                    ),
                    "Known[X]", 'Date'[Date],
                    "Known[Y]", [Percentage of Abnormal Results]
                ),
                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
            AVERAGEX (
                DISTINCT ( 'Date'[Date] ),
                Intercept + Slope * 'Date'[Date]
            )
    )
RETURN
    Result

Regards

  • Supi's avatar
    Supi
    New Member
    1 year ago

    Many thanks for such a brilliant solution. It works perfectly with my use case.