Forum Discussion

emma313823's avatar
emma313823
Helper V
2 years ago
Solved

trend line help

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.

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

9 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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's avatar
      emma313823
      Helper V
      2 years ago

      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's avatar
        Jihwan_Kim
        Super User
        2 years ago

        Hi,

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

  • emma313823's avatar
    emma313823
    Helper V
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.