6 Replies Latest reply on Feb 14, 2018 3:08 PM by Jim Dehner

# Calculate rolling LTM using DATEDIFF to work in a table

Hi all,

New to the forum. Thanks for helping.

I want to calculate Customer Base Value as the current month's CMRR plus the sum of Transaction revenue LTM minus the sum of COGS LTM. See screenshot below.

I have read other forum entries like this one, but I can't find any way to have the LTM value dynamically calculated as a field in a table.

I need to know the Transaction LTM for all months (so in March 2017, the sum of Transaction from April 2016 through March 2017).

Is this possible?

Attached an attempt in the sample workbook below.

Thanks!

• ###### 1. Re: Calculate rolling LTM using DATEDIFF to work in a table

Hi

Not certain what you are doing but see the calculation below for rolling sum

WINDOW_SUM(sum([Sales Target]),-11,0)

this will give you an R12 - you need to put in the limits - your calculation for last month is not correct   it should be dateadd('month',-1,[Order Date]) but I don't know where or if you would use it

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Calculate rolling LTM using DATEDIFF to work in a table

Hi Anders,

Find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.

1. "Proof of the Pudding";)

2. Define Custom Date NM D1. Order Date (Month/Year)

3. NM M1. Sales LTM: window_sum(sum([Sales Target]),-11,0)

//calculate the sum over the last 11 month & current month for each month

4. Drag the required objects to the indicated locations

5. NM M1 Sales LTM > Right Click > Text menu > Compute using > NM D1. Order Date (Month/Year)

Regards,

Norbert

• ###### 3. Re: Calculate rolling LTM using DATEDIFF to work in a table

Thanks! Saw this now.

WINDOW_SUM is what I need - not sure how I could miss that.

Thanks!

• ###### 4. Re: Calculate rolling LTM using DATEDIFF to work in a table

Anders

Glad to help out - when you write a calculation the box that opens has a category level - when you open it you get just the formula in that category

when working with table just look at the table calcs and find the one that applies

jim

1 of 1 people found this helpful
• ###### 5. Re: Calculate rolling LTM using DATEDIFF to work in a table

Perfect, thanks again, Jim.

Anders