LAG function

DMManalili
Frequent Contributor

LAG function

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 🙂

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHeathcote
Community Boss

Re: LAG function

@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,

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

1 REPLY 1
ChrisHeathcote
Community Boss

Re: LAG function

@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,

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post