Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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
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)
Perfect that works great
Thanks Sunny