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 each calendar year we have a seperate (Legend) color.

The challenge is that the Sales data is from 3 different [Channel] -s, let's call them A, B and C.

B and C are always up-to-date, whereas channel A is (usually) 1 month behind.

Depending on how many channels you filter, it will add onto the **bleep**. sales total.

The calculation for the sales is:

CALCULATE (
    SUM ( Sales[Sales]),
    FILTER ( ALL ( dates[Month]), dates[Month] <= MAX(dates[Month]))
)
 
The issue is that when you include A in the filtering, it'll show a wrong **bleep**. total, as it doesn't have any data for the most recent month. 
 
What I would like to achieve is:
- When A is filtered (with/without B / C), let the line chart show the most recent date from A
- When A is NOT filtered, show line chart from the most recent sales date from B/C
 

 

Current line chart where A is filtered and shows a wrong value for the third month

 

 

Expected result:

 

Same as above, but capped off at the second month, as A doesn't have any sales data for the 3rd month.

 

Other way around:
If A Isn't selected, show till 3rd month, as B and C do have data till the latest month.

 

 

Any solution for this?

Like to hear from you,

Daniël

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. 

3 Replies

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