trend line help

[emma313823]

All,

Need some help on something that I am unable to figure out. There are 2 primary end goals.

1. I have a line visual chart i've created to show 2022 to 2024. If you look at the chart the pink line is 2024. It is plotting data through August as I get new detail, then goes flat from September on. My CEO has asked if there is a way to plot data only through months that have data? i.e. we are still in August, so the chart would display Jan-Aug only. When data begins to arrive in September, then September would show on the chart.

Not sure if this could be achieved through a measure or if at all.

X-Axis is MONTH

Y-Axis is sum of REVENUE

Legend is YEAR

To take this a step further...he'd also like to have the oldest year automatically drop off, so it always shows the current year plus the two previous years.

On the Data pane...these are the tables and columns being used.

Hope a BI guru can help me.

[Anonymous · Accepted solution]

Hi @emma313823 ,

Thanks Jihwan_Kim  for the quick reply and solution. His solution is good!

I have some ideas to add:

(1) Create two columns on Table[Acctg Revenue].

Year = YEAR([Date])

MonthNum = MONTH([Date])

(2) Create measures.

Sum Revenue = CALCULATE(SUM('Acctg Revenue'[Revenue]),FILTER(ALL('Acctg Revenue'),[Year]=MAX('Calendar_Dates'[Year]) && [MonthNum]=MAX('Calendar_Dates'[MonthNum])))

Max Month = 
var _max_year=MAXX(ALL('Acctg Revenue'),[Year])
var _max_month=MAXX(FILTER(ALL('Acctg Revenue'), [Sum Revenue]<>0 &&[Year]=_max_year),[MonthNum])
RETURN _max_month

Flag = 
var _max_year=MAXX(ALL('Acctg Revenue'),[Year])
var _2_ago=_max_year-2
RETURN IF(MAX('Calendar_Dates'[Year])<=_max_year && MAX('Calendar_Dates'[Year])>=_2_ago && MAX('Calendar_Dates'[MonthNum])<=[Max Month],1,0)

Set [Flag=1] in the Filter Pane.

Best Regards,

Neeko Tang

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

[emma313823]

Hi Guys...

Thanks for your patience. I'm still very new to this and am struggling with it quite badly. Would I be able to send the pbix file or at minimum the excel file that this is being used for this visual?

Emma

[Anonymous]

Hi @emma313823 ,

Can you download my attached PBIX file? In my sample the exact same table name as yours.

For more details, you can refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Neeko Tang

[emma313823]

Would anyone else be able to help solve how to fix this issue based on my last response? HELP PLEASE

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

In the linechart, 2024 data shows upto August & it only shows the latest three years information.

expected result measure: =
VAR _latestthreeyears =
    WINDOW (
        1,
        ABS,
        3,
        ABS,
        SUMMARIZE ( ALL ( attg_revevue ), 'calendar'[Year] ),
        ORDERBY ( 'calendar'[Year], DESC )
    )
RETURN
    CALCULATE (
        SUM ( attg_revevue[sales] ),
        FILTER ( 'calendar', 'calendar'[Year] IN _latestthreeyears )
    )

[emma313823]

Hi

So I tried to make changes to align to my tables and column names, but get an error.

I didn't change anything except for my table and column names.

Emma

[Jihwan_Kim]

Hi,

can you try writing 'Acctg Revenue' with ' in the front and   ' in the end ?

[emma313823]

Hi,

I think that fixed that part, but still not working.

YrlyRevUpToCurrMo =

VAR _latestthreeyears =

    WINDOW (

        1,

        ABS,

        3,

        ABS,

        SUMMARIZE ( ALL ( 'Acctg Revenue'[Revenue] ), 'calendar_dates'[Year] ),

        ORDERBY ( 'calendar_dates'[Year], DESC )

    )

RETURN

    CALCULATE (

        SUM ( 'Acctg Revenue'[Revenue]),

        FILTER ( 'calendar_dates', 'calendar_dates'[Year] IN _latestthreeyears )

)

shows this:

My tables and columns being used are:

 

[Jihwan_Kim]

Hi,

I think you need to write table name, not the column name for the first argument in the SUMMARIZE DAX function.

Also, there is the difference between what you copy-pasted & screenshot.