LAG function

DMManalili
DMManalili
in Modeling

Hi,

Question please. I am doing a variance analysis module which line items composed of Last Year (LY) Actual, Actual (current), Forecast, Budget and the variances line items. However, I am trying to have LAG function for the LY Actual line items. I am getting the numbers if I choose a specific month (e.g. Feb21) in the filter but when I choose FY (e.g. FY21), I am not getting the exact values. I used SUM, CLOSING Balance in the summary method or SUM  but still cannot get the values.

Source Module : P&L

DMManalili_1-1617605513795.png

Target Module : Variance Module

DMManalili_2-1617605522182.png

in the LY Actual column - I should be getting Jan 38.65 + Feb 26.90 = 65.55

What should be the correct summary method? or formula?

 

Thank  you 🙂

 

 

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    @DMManalili 

    The FY LY is the full year value not just Jan and Feb.

    Also, use SUM as the summary method.

    Try setting your current period to Feb21 and select YTD to pull through Jan and Feb last year.

    Good luck,

Categories