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.

Reply
noob123newbie
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. 

pics.png

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

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 )

vyiruanmsft_0-1706603517530.png

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.

View solution in original post

4 REPLIES 4
noob123newbie
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
    )

  

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 )

vyiruanmsft_0-1706603517530.png

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.
Ritaf1983
Super User
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

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 SalesMonthDateValueFY
Dell LaptopAug 202308/01/2023342023
Dell LaptopAug 202308/11/20237752023
Lenovo LaptopSep 202309/09/20239862023
Lenovo LaptopSep 202309/04/20232342023
Dell LaptopOct 202310/23/2023872024

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.