Dynamic Max date for line chart

[Foxxon28]

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

[v-tangjie-msft]

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]

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

[v-tangjie-msft · Accepted solution]

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.