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

          Jim

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

             

            IIF(YOURTIMEDIMENSION>=DATETRUNC('month',dateadd('month',-1,TODAY()) and YOURDATEDIMENSION<DATETRUNC('month',TODAY())

            ,YOURMEASURE,0)

             

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

             

            Thanks,

             

            Mia

            • 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

                 

                Jim

                • 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])}

                   

                   

                  Jim