get selected max in set analysis

[wanyunyang]

I have Posting Years 2022,  2021,  2020, Order Years 2022, 2021, 2020, and Quantity Sold.

In my formula, after I select a year, I want to see the quantity sold for previous 1 year. Below is the formula I use:

sum({<[Posting Year]={"$(=MAX([Posting Year])-1)"},[Order Year]={"$(=MAX([Order Year])-1)"}>}Quantity)

But the problem is, when I only select Posting Year =2021, I want to see 2020 posted and all-time ordered, but this formula gives me 2020 posted and 2021 ordered even though I don't select Order Year.

Does anyone have any idea how to fix this formula? Thanks in advance!

[RafaelBarrios]

Hi @wanyunyang 

That’s because you have 2021 orders posted in 2020

you can try

sum({<[Posting Year]={"$(=MAX([Posting Year])-1)"},[Order Year]={"$(=MAX([Posting Year])-1)"}>}Quantity)

Or use a variable

vMaxOrderYear
=if(getSelectedCount([Posting Year])=0,max([Posting Year]),max([Order Year]))

and then

sum({<[Posting Year]={"$(=MAX([Posting Year])-1)"},[Order Year]={"$(=vMaxOrderYear)"}>}Quantity)

Hope this helps
It could have some syntax errors, couldn’t tried

best,

Help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂