1 Reply Latest reply on May 10, 2017 12:40 PM by Elliott Stam

# Facing Problem in Calculating Amount for Previous Month

I have a ref date field as below. Last 13 months end date based on Today's Date

When no date is selected, it should take max of Ref Date and compute Previous month amount.

Suppose for 9th may, it should take dates for 01/04/2017 to 30/04/2017 and return the amount.

If I select a date, For EX: 30/04/2017, then it will take Mar 1st to Mar End and compute the amount.

Could someone help me on this??

• ###### 1. Re: Facing Problem in Calculating Amount for Previous Month

Hi Panda,

You'll want to create two formulas...

Max Date:

{ FIXED : MAX([Ref Date])}

*the above formula computes the max "Ref Date"

Your primary calculation would be something like this (assuming your date control is a parameter)...

New_Calculation:

IF [Date parameter] = "No Date"

THEN

IF

DATETRUNC('month', [Ref Date]) = DATETRUNC('month', DATEADD('month', -1, [Max Date]))

THEN [Amount]

ELSE 0

END

ELSE

IF

DATETRUNC('month', [Ref Date]) = DATETRUNC('month', date([Date parameter]))

THEN [Amount]

ELSE 0

END

END

Hope that helps,

Elliott Stam - InterWorks