Forum Discussion

noob123newbie's avatar
noob123newbie
Frequent Visitor
2 years ago
Solved

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

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

4 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    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's avatar
      noob123newbie
      Frequent Visitor
      2 years ago

      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
  • noob123newbie's avatar
    noob123newbie
    Frequent Visitor
    2 years ago

    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
    )

      

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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