Forum Discussion

Foxxon28's avatar
Foxxon28
Helper I
2 years ago
Solved

Dynamic Max date for line chart

Dear readers,   I have 2 tables: 'Sales'  'Date'   Currently I have a line chart that shows the cumulative sales, per month.  On the X axis, I have 12 months, Y axis is Sales amount.   For ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Foxxon28 ，

     

    You can update the measure.

    Flag = 
var _a=IF(ISFILTERED('Slicer'[Channel]) && {"A"} in VALUES('Slicer'[Channel]),1,0)
var _year_b=YEAR(MAXX(FILTER(ALL('Sales'),[Channel]="B"),[date]))
var _month_a=MONTH(MAXX(FILTER(ALL('Sales'),[Channel]="A"),[date]))
RETURN SWITCH(TRUE(),
_a=1 && MAX('Date'[year]) < _year_b,1,
_a=1 && MAX('Date'[year])=_year_b && MAX('Date'[month])<= _month_a,1,
_a=0,1,0)

    Place [Flag=1] on the visual object screening and then the result is as follows.

     

    Regarding this variable _month_b, I apologize that this was an oversight on my part and has now been removed.

     

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

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a slicer table and a measure. 

Slicer = VALUES('Sales'[Channel])
Flag = 
var _a=IF(ISFILTERED('Slicer'[Channel]) && {"A"} in VALUES('Slicer'[Channel]),1,0)
var _month_b=MONTH(MAXX(FILTER(ALL('Sales'),[Channel]="B"),[date]))
var _month_a=MONTH(MAXX(FILTER(ALL('Sales'),[Channel]="A"),[date]))
RETURN SWITCH(TRUE(),
_a=1 && MAX('Date'[month])<= _month_a,1,
_a=0,1,0)

(3) Place [Flag=1] on the visual object screening and then the result is as follows.

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. 

  • Foxxon28's avatar
    Foxxon28
    Helper I
    2 years ago

    Dear Neeko Tang,

     

    Thank you for the swift reply.

    This does work, however it doesn't seem to work quite well for historical (year) data.

    When filtering A It now correctly shows its max month, but it does so for all years. Where I expect years 2018-2023 to have 12 months of data.

    Any fix for this?

    Also curious: What do you end up using the variable "

    var _month_b=MONTH(MAXX(FILTER(ALL('Sales'),[Channel]="B"),[date]))

    " for? It doesn't look like it's used in the RETURN statement

     

    Kind regards,

     

    Daniël

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Foxxon28 ，

       

      You can update the measure.

      Flag = 
var _a=IF(ISFILTERED('Slicer'[Channel]) && {"A"} in VALUES('Slicer'[Channel]),1,0)
var _year_b=YEAR(MAXX(FILTER(ALL('Sales'),[Channel]="B"),[date]))
var _month_a=MONTH(MAXX(FILTER(ALL('Sales'),[Channel]="A"),[date]))
RETURN SWITCH(TRUE(),
_a=1 && MAX('Date'[year]) < _year_b,1,
_a=1 && MAX('Date'[year])=_year_b && MAX('Date'[month])<= _month_a,1,
_a=0,1,0)

      Place [Flag=1] on the visual object screening and then the result is as follows.

       

      Regarding this variable _month_b, I apologize that this was an oversight on my part and has now been removed.

       

      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.