Conditional expression based on dimension

[Konstantin_Novikov]

Dear all,

There is a chart showing monthly price dynamics for two years (current & previous).
There are also two straight lines showing avg price of current year and previous year.

On the same sheet user can set filters by month and year, but these filters do not affect the chart. It always shows full max year from the selection + the previous one (i.e. 2019 + 2018, 2018 + 2017, etc).
All other selections should be applied to the chart. I.e., if user needs data of specific product or customer.

Is it possible to show Avg CY line only for this year, and Avg PY line only for prev.year? (see example below)

I tried If function "if(Year = max(Year), then)", but it makes Year & month selections applied:
If March is selected, it shows single point for March;
If only 2019 year is chosen, there is nothing shown for 2018.

Any advice on the topic is appreciated.
Thank you!

[anushree1]

use total keyword in your avg expression

like avg(total expressiondefinition)

[olivierrobin]

did you try

year=max({1}year) ?

[Konstantin_Novikov]

Thank you for your answers. I got some progress using max({1}Year). Now the graph is perfect if 2019 year is chosen and there is no selection by month.

There are two problems now:
1) If 2018 is chosen, PY line disappears;
2) If there is a selection by month, PY line is shown for both years, and CY line is shown only for selected months

Current expressions are the following:

Avg CY:

If(Calendar.Year = max({1}Calendar.Year),
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year))'} > }Sales)
/
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year))'} > }Volume)
)

Avg PY:

If(Calendar.Year <> max({1}Calendar.Year),
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year)-1)'} > }Sales)
/
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year)-1)'} > }Volume)
)

[olivierrobin]

hello,

in your set analysis, try to add

Calendar.Month=

which will inhibit all selections made on month

[Konstantin_Novikov]

There are two fields: Calendar.YearMonthName and Calendar.MonthName.
The first one is dimension on X axis, and the second one is used by users for period selection.
In my expressions there are already "Calendar.MonthName = " identifiers. I also tried adding "Calendar.YearMonthName = ", but both options (and combination) do not work.