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 - 1...
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

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