Set Expression with P for prior year

[Gaz4OakM]

Im trying to get a sum of sales for all account IDs that has mains for a prior year

Sum({<
Year={$(=(v.Date.Year.Max)-1)},
[AccountID] = P({<[Product Category]={[Mains]}>})
>}Sales)

If i exclude the Year section it gives me the values for this year however once i add the year -1 it throws me out Zeros. Any help will be greatly apprectiated

[sunny_talwar]

Have you made a selection in date or any other date and time related field which corresponds to the max year? For example, 01/01/2019 is selected in date which is only a date from 2019? If you do, then you would need to ignore selection in it

Sum({<
Year={$(=(v.Date.Year.Max)-1)},
Date,
[AccountID] = P({<[Product Category]={[Mains]}>})
>}Sales)

[Gaz4OakM]

Thanks for the super quick response, however i have Year selected as 2020 in my filters. your expression just gives me the 2020 values (this year)

however the expression

Year={$(=(v.Date.Year.Max)-1)}

is included in other tables / charts within the sheet and its pulling the 2019 data.

Is the "p" in the expression throwing this out because its the only thing different in any expressions i have

[sunny_talwar · Accepted solution]

Don't think it should, but you can try this

Sum({<
Year = {$(=(v.Date.Year.Max)-1)},
[AccountID] = P({<[Product Category] = {[Mains]}, Year = {$(=(v.Date.Year.Max)-1)}>})
>} Sales)

[Gaz4OakM]

Perfect that works great

Thanks Sunny