Max Year and Previous Year in Straight Table

[User12321]

Hello,

I have a year field and I only want the max year and previous year to appear in the Straight Table.

Any suggestion please? Thanks!

[anthonyj · Accepted solution]

Hi @User12321 ,

If you have a column with just the year in it you can use a condition like below to return the last 2 in a dimension.

=if(Year >= max(total Year,2), Year)

Then untick the "include null values" box in the column.

Thanks

Anthony

[Vegar · Accepted solution]

The solution of @anthonyj will reduce the availabe dimensions to the two largest in your selection. Note that both the years need to be available in your selection for it to work.

An alternative is to adjust your expression instead of your dimension by using set analysis. Assuming your current expression is SUM(Amount) then the new would be SUM({<Year = {">=$(=max(Year)-1)"}>}Amount)

[anthonyj]

You're absolutely right @Vegar . I try to avoid "if" conditions whenever possible as it is better to subset your data via a set analysis in your measure. The only reason I did it in the dimension was that @User12321  mentioned that, from the problem statement it appears a measure is not available. Although I have created a dummy measure in the past so I could use set analysis and set the colour to white so it couldn't be seen in the table. Looking back I don't know if that was a good move or not.

Thanks

[User12321]

Hi @anthonyj and @Vegar,

Thanks for all the inputs.

[anthonyj]

Hi @User12321 ,

If you're going to use it in a dimension then you have to use:

=if(Year >= max(total Year,2), Year)

Only use dollar expansion and the curly braces If it's to be used as part of a measure.

Regards

Anthony