YTD - Set Analysis Exclusion

[#1]

Below is a working formula that I am using to get the SUM of my Previous Year:

=sum({$<YEAR= {$(vPrevYear)}, [Acc Month] = {"<=$(=max({<[Acc Year]={$(=max([Acc Year]))}>} [Acc Month]))"} >} Line_Pending_Count)

My issue is that I have a filter box using the [YEAR] field listing the current years in the data (2016, 2015). When "2016" is selected in the filter box the formula above is correct, but when "2015" is selected it is taking the SUM of the entire year (2015). I know this is because [Acc Year] is being modified when making a selection to the [YEAR] filter box. So for example: if I select [YEAR] = 2015... the MAX([Acc Year] is now 2015.....

My question is how do I override this in the formula above? I would like max[Acc Year] to always be the TRUE max. I know you can place a {1} in front of the field, but I can't seem to get that to work in the particular formula.

Any suggestions would be greatly appreciated!

Thanks

[krishnacbe]

Try Adding the selection exclusion for the [Acc Year] field.

Hope below expression helps

=sum({$<[Acc Year]=, YEAR= {$(vPrevYear)}, [Acc Month] = {"<=$(=max({<[Acc Year]={$(=max([Acc Year]))}>} [Acc Month]))"} >} Line_Pending_Count)

[sunny_talwar · Accepted solution]

Try this:

=Sum({$<YEAR= {$(vPrevYear)}, [Acc Month] = {"<=$(=max({1<[Acc Year]={$(=max({1}[Acc Year]))}>} [Acc Month]))"}>} Line_Pending_Count)

[#4]

Works !!!! Thank you!!