Forum Discussion

emma313823's avatar
emma313823
Icon for Helper V rankHelper 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 p...
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. 

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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
Icon for Helper V rankHelper 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
    Icon for Super User rankSuper User
    2 years ago

    Hi,

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

    • emma313823's avatar
      emma313823
      Icon for Helper V rankHelper V
      2 years ago

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

        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.