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

# Formula with a month Lag

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

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

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,

,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

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

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

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