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

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