what would be the formula for referring Previous year value in current year ?
For example, I want to refer the value of last year july 2022 in current month July 23. the line item is Past year revenue where I just want to show same month revenue from previous year, how could I achieve that?
Best Answer
-
movingsum() will perform better
0
Answers
-
Have you tried using Movingsum()?
0 -
I think its the OFFSET function, got the desired values. Thank you for response :)
0 -
Oh ok.sure will try that as well. Thank you
0 -
@rob_marshall how movingsum would be performing better than lag in this case when only one number needs to be calculated from previous periods?
0 -
Because MovingSum(line item,-12,-12) will perform faster than Lag(line item,12, STRICT/SEMI STRICT) which will perform better than LAG(line item,12).
0 -
@rob_marshall so based on this it would be best practice to never use LAG/OFFSET as you can achieve same functionality with MovingSum which would always be performing better even if it's only one time item at a time you want to get?
0 -
In this case, LAG(line item, 12) and MovingSum(line item, -12,-12) will return the same number, which in this case is the same member of the previous year which is what the original poster was asking. But, to your question, movingsum does perform better than lag/next().
0
