5 Replies Latest reply on Feb 24, 2017 2:32 PM by Jim Dehner

    Formula with a month Lag

    Maria Trumble

      I have created a summarised Income statement, made up of many different measures.


      I am so close tot he bottom of the statement and I have exhausted the internet with my searches.


      I have to create a formula for capital charge.


      This would be net assets (Prior Month) * % / 12


      I have the formula for Net Assets.


      However The filters I have on the data show for current month.


      Is there a way for me to write a formula that looks at the filtered month  and lags the net assets value?


      I have managed to produce the data on a different workbook, but I really would prefer to have it on the summarised income statement instead


      Any help is greatly appreciated

        • 1. Re: Formula with a month Lag
          Jim Dehner

          Hi Maria


          I understand confidentiality - makes it tough to see your formula -

          best guess is to try a Lookup() function - with the increment of -1 -


          go to the Calculated Field wizard and follow the format there to insert your capital charge expression


          let me know if it works


          • 2. Re: Formula with a month Lag
            Mia Lee

            Hi, Maria


            Yes you can - if I understood it right, you can create a calc. field that filters last month data only. We don't have a workbook, so I can't write an exact formula for you but it should write something like,





            Just add YOURTIMEDIMENSION and YOURMEASURE in the calc above. Please let me know if you have further questions.





            • 3. Re: Formula with a month Lag
              Maria Trumble

              In the capital charge tab I have just the lookup -1 function to give the lag amount and then used the capital charge formula to calculate the amount.


              However in the summarised income statement because the month is a filter I can't seem to use that same type of formula.


              Is there a way to write the formula in the summarised income statement using the filtered month?


              Unfortunately I am not able to share the workbook, so screen shots is the best I can do.

              • 4. Re: Formula with a month Lag
                Jim Dehner

                I don't know - how complex is the formula -


                I typically write the formula for each piece of the calculation separately then put them together


                For totals a statement like >>{fixed : sum(field)} often


                see below - this is a formula I just did for someone who wanted the max value across a total line that spanned several markets


                          >>{ FIXED :Max({ FIXED [Markets]:(sum([values]))})}


                essentially it says to take the max of the max



                • 5. Re: Formula with a month Lag
                  Jim Dehner

                  sorry I was interrupted and hit send too soon - this calc did the sum of the columns in his viz then I divided this one by the previous MAX one to give him the answer he needed


                  >>{ FIXED [Markets]: sum([values])}