cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Kudo Commander

## Measure that reads the first and last values of trend line

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.

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Kudo Commander

Thank you  @v-xicai that worked well!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors