Skip to main content
cancel
Showing results for 
Search instead 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

Reply
vpatel55
Kudo Commander
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.

 

Picture.png

 

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
v-xicai
Community Support
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.

View solution in original post

2 REPLIES 2
v-xicai
Community Support
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.

Thank you  @v-xicai that worked well! 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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