How to Pull Amounts from Prior Periods and Place it to a Module Using a Different Time Range

jspascual
jspascual
in Modeling

Hello, 

 

Good day to you, all. 

 

I would like to ask this question that's been bugging me for days now. 

 

Situation: 

 

1. I have a module that houses actual data primarily used for uploading a csv file. This does NOT have time dimension as the the period is only a line item. But since the model calendar is from 2018 to 2022, it can house data way back 2018. 

jspascual_0-1660951774280.png

2. I have another module that is always in the current year (2022)-- so this is using a Time Range from Jan 22 to Dec 22. In this module, we are putting in for example a projected savings from Jan 22 to Sep 22. The amount is spreading as expected.

jspascual_1-1660951831595.png

 

3. There is a module which serves as the baseline input. Users put the baseline start and baseline end period. For example, user puts Jan 19 - Sep 19 on this module.

 

4. The challenge is, how to compare the two modules using different time dimension. The actual spread coming using model calendar from 2018-2022, and the other module from #2 is using always the Current Year, 2022. How to pull the amounts from  Jan 19 to Sep 19 and place the amounts to Jan 22 to Sep 22 so they can be compared?  

 

Thanks a lot for your ideas.

 

Regards,

 

Jon

Tagged:

Best Answer

  • jspascual
    jspascual
    Answer ✓

    I meant to reply on this to update...

     

    I think it's working now. I used the LAG function and use a system module to input baseline start and end, and calculated in the same module what would be the number of periods it would do the LAG for automatically.

     

    Thanks for all the ideas. 

     

    -Jon

Answers

Categories