Modeling

AmruthaB

HI,  Can someone help the below requirement.  Need to get Prior year data, I know to use offset or lag, But for FY, I need to get the data what i have in the prior year (FY) only, when using offset or Lag , I cant use any   other summary except "sum".  Any help would be highly appreciated. 

Thanks

 

 

ChrisAHeathcote

@AmruthaB 

If you target module is not dimensioned by time you can use a LOOKUP function.

Create a systems module to capture all your current time data. Keep the module free of all dimensions including time and create two line items. Format these as time period year. 

Name then current year and prior year. 

In current year use =PERIOD(CURRENTPERIODEND())

In the prior year line item use =Current Year - 1

Now use the prior year line item to LOOKUP the value from your source module.

 

If you target module is dimensioned by time period year then create a time settings year module dimensioned only by year.

Create a line item dimensioned by time period year. 

Using the following =ITEM(TIME)-1 create a mapping where each year is mapped to the corresponding prior year. Use this to inform your LOOKUP.