Dear Team, I have a business scenario (refer to attached file). The Formula for Net Volume is not working because Returns % are maintained by Year however even if the Returns % are maintained by Months it is not picking up the values In addition to above, YEARVALUE does not allow selection using SELECT or LOOKUP as parameter. For Example: YEARVALUE (Returns (LOOKUP: REGION) OR YEARVALUE ( Returns) [LOOKUP: REGION) gives error messages Given above I could ask Users to maintain the returns % by month, add a formula as following to read return % IF Customers.Territory = Territory.US THEN Returns[SELECT: Territory.US] ELSE 0 Question is there is a better way to achieve above? Thanks for reading my post (refer attachment) and look forward for your suggestions! Cheers, Umesh
Hi Umesh -
I'd be inclined to just have a separate line item named something like 'Returns % by month' which is split by Territory and Month. This would just have the formula YEARVALUE(Returns).
Your net volume formula would then just be
Net Volume = Gross Volume * (1 - Returns % by Month[LOOKUP: REGION])
A nice extension on this might be to allow the users to override annual returns value in any individual month, i.e. have three line items in the Territory x Month module...
'Returns % by month' = YEARVALUE(Returns)
Returns Override which is data input
Finalised Returns = IF Returns Override <> 0 THEN Returns Override ELSE 'Returns % by month'
Hope this helps.