Differnce between LEAD and LAG function


I am trying to figure out the difference between LEAD and LAG function; as we can get same value from future and past period by using both function. 


for Ex: LEAD(x,-2,1000,STRICT) and LAG(x,2,1000,STRICT) will get the result from 2 period before the current cell.

Can someone help me where we should use LEAD function .and not LAG function and vice versa






  • Kindly ignore STRICT mode and treat it as NONSTRICT
  • @Ankit_Negi 

    Ha! Great question. To be honest they are the same in terms of performance so my only suggestion would be to use PLANS methodology and use the function that describes what you're trying to do. If you want the value that lags then use LAG. If you want the value that is a leading value then use LEAD. 

    You'll also find that OFFSET can be used too. Here's an awesome discussion from the Anaplan Masters on this subject of which performs the best.