Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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
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])
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
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])
Hi Oleg,
Indeed this shows that 2023 is in your calendar. Nice solution!
Jordy
Climber