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. 

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. 

  • emma313823's avatar
    emma313823
    Helper V
    2 years ago

    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