
1. Re: Formula with a month Lag
Jim Dehner Feb 24, 2017 1:59 PM (in response to Maria Trumble)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 Feb 24, 2017 2:02 PM (in response to Maria Trumble)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 Feb 24, 2017 2:19 PM (in response to Jim Dehner)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 Feb 24, 2017 2:27 PM (in response to Maria Trumble)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 Feb 24, 2017 2:32 PM (in response to 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