Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Custom Trend Line on Bar Chart

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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Frequent Visitor

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
)``````

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

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

Frequent Visitor

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

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors