How to limit the bar chart dimention (Years)

[oleggggggM]

Hello,

I have a bar chart showing spending evolution during a few years. And I am using the Trend Line (linear) to show the general trend. Now, as 2022 started and no (limited) spend was posted yet, the trend line is negatively affected.

I would like to limit the chart so as to show ALL previous years but EXCLUDING the current one (e.g. 2022).

I was playing with "limitation" for the dimension but could not achieve the required result. The X-axis is populated with Date (which is the actual booking date) and I am using auto.calendar to derive the year as the dimension. Any advice on how can achieve the goal, or should I just look for a different Trend Line?

thank you for your help in advance

[JordyWegman]

Hi Oleg,

You could use set analysis to exclude your current year. Look at the use of the - that excludes it.

Sum( {$< Year -= {&(=Max( Year ) ) }>} Value)

Jordy

Climber

[oleggggggM]

Thanks Jordy, 

Yes, I tried that as well, however, no success... not sure why though

The formula I use:

sum({<[Date.calendar.Year]-={$(=Max([Date.calendar.Year]))} >} [Actual Amount])

[JordyWegman]

Hi Oleg,

That calendar could be a bit of an issue. Try Max( Year) in a table without any selections. What does this give you? It could say 2023 or higher. If so, you should limit this calendar to 2022,

Jordy

Climber

[oleggggggM · Accepted solution]

Jordy, thank you for pointing me in to correct direction. Rather than using dimension limitation, I have used measure set analysis to exclude the last year. Even your formula does not work, I used a slightly adjusted version and it looks good now.

sum({<[Date.calendar.Year]={"<$(=Max([Date.calendar.Year]))"} >} [Actual Amount])

[JordyWegman]

Hi Oleg,

Indeed this shows that 2023 is in your calendar. Nice solution!

Jordy

Climber