Custom Trend Line on Bar Chart

[noob123newbie]

Hi guys,

I have this Dashboard that i am working on, i am looking to draw this trend line where I need to show a 20% increase from the previous month. 

E.g.

Aug 2023 - 72

Sep 2023 - 126

So the trend line should start using the Aug 23 value of 72 x 20% increase that will be 86(Rounded) and the Trend line should start from Sep 23 onwards.

Not sure how to get this done via DAX, hence require some assistance. 

Cheers11

[noob123newbie]

Hi there,

i tried using the following DAX formula but not getting the desire result.

Trendline =
    VAR PreviousMonthSum = CALCULATE(
        SUM('Sales'[Value]),
        DATEADD('Sales'[Date], -1, MONTH)
    )
    RETURN
    IF(
        ISBLANK(PreviousMonthSum),
        BLANK(),
        PreviousMonthSum * 1.2
    )

[v-yiruan-msft · Accepted solution]

Hi @noob123newbie ,

You can update the formula of measure [Trendline] as below, please find the details in the attachment.

Trendline = 
VAR _curmonth =
    SELECTEDVALUE ( 'Sales'[Month].[MonthNo] )
VAR _curyear =
    SELECTEDVALUE ( 'Sales'[Month].[Year] )
VAR _date =
    EOMONTH ( DATE ( _curyear, _curmonth, 1 ), -1 )
VAR PreviousMonthSum =
    CALCULATE (
        SUM ( 'Sales'[Value] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            YEAR ( 'Sales'[Month] ) = YEAR ( _date )
                && MONTH ( 'Sales'[Date] ) = MONTH ( _date )
        )
    )
RETURN
    IF ( ISBLANK ( PreviousMonthSum ), BLANK (), PreviousMonthSum * 1.2 )

Best Regards

[Ritaf1983]

Hi @noob123newbie 
According to your description, a measure for the line should be 
Trend_measure = [your measure ]*1.2
If it is something else 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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

[noob123newbie]

Hi there,

Please find sample data. 

I am using the current Trend Measure formula, but i would have to start the trend line from Sept based on Data from Aug 2023 * 1.2.

Bar Chart :

X-Axis : Date(Month)

Column Y-Axis : Sum of Value

Line Y-Aix : 20% Increase(Trendline)

Column Legend : FY

Desc of Sales	Month	Date	Value	FY
Dell Laptop	Aug 2023	08/01/2023	34	2023
Dell Laptop	Aug 2023	08/11/2023	775	2023
Lenovo Laptop	Sep 2023	09/09/2023	986	2023
Lenovo Laptop	Sep 2023	09/04/2023	234	2023
Dell Laptop	Oct 2023	10/23/2023	87	2024