Measure that reads the first and last values of trend line

[vpatel55]

I have produced a line chart with a trend line. The trend line is a custom measure, and not the in-built trend line.

I created two measures that read the earliest and latest values from this trend line, called "First Invoice Trend Price" and "Last Invoice Trend Price".

However, the two measures are giving unexpected results, in that they return the first and last value from the data line, and not the trend line. This is even though the expression refers to the trend line. For example, the first invoice value is showingas £51.43, when the expected value (the trend line) should be £46.70.

Here is the measure that attempts to read the value from the first date:

First Invoice Trend Price =
CALCULATE (
    [Invoice Average Trend],
    FIRSTDATE ( 'Calendar'[Date (Month)] ),
    ALLSELECTED ()
)

The file can be downloaded from here:

https://www.dropbox.com/s/5bhh2y5pk0uro3d/Sample%20file.pbix?dl=0

Here is the code for the trend line:

Invoice Average Trend = 
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'Calendar'[Date (Month)]),
            "Known[X]", 'Calendar'[Date (Month)],
            "Known[Y]", [Invoice Average]
        ),
        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 ( 'Calendar'[Date (Month)]),
        Intercept + Slope * 'Calendar'[Date (Month)]
    )

Any help would be appreciated. Thanks.

[v-xicai · Accepted solution]

Hi @vpatel55 ,

You may create measures like DAX below.  (note that your pbix file link may be invalid).

First Invoice Trend Price =

Var MinDate= CALCULATE(MIN(Calendar[Date(Month)]), ALLSELECTED(Calendar[Date(Month)]))

Return

CALCULATE ([Invoice Average Trend],FILTER(ALLSELECTED ('Invoice table'), 'Invoice table'[Date]= MinDate ))



Last Invoice Trend Price =

Var MaxDate= CALCULATE(MAX(Calendar[Date(Month)]), ALLSELECTED(Calendar[Date(Month)]))

Return

CALCULATE ([Invoice Average Trend],FILTER(ALLSELECTED ('Invoice table'), 'Invoice table'[Date]= MaxDate ))

Best Regards,

Amy 

Community Support Team _ Amy

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

[vpatel55]

Thank you  @v-xicai that worked well!