6 Replies Latest reply on Sep 26, 2018 3:48 PM by Angela Blackburn

# Date calculations using MM YYYY Only

I am trying to fix a dashboard that has rolling months built into the calculations.  First I need to get my parameter swappers to work.

The field [Fiscal Date] is already formatted as a Date Dimension with the format 'May 1 2016' for example.

The field [FTS Paid] is a Measure.

I am trying to do a simple calculation.

Here is my FTS Paid 1-24 Calculation:

sum(IF [Fiscal Date]  >= dateadd('month',-1,TODAY()) and [Fiscal Date]  <= dateadd('month',-24,TODAY())

THEN [FTS Paid] else 0 END)

However when it keeps returning 0.

What am I doing wrong?

• ###### 1. Re: Date calculations using MM YYYY Only

Hi in words the conditional reads if the date is greater than last month and also less than 24 months ago  -- think you less than and greater thans are turned around

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.

• ###### 2. Re: Date calculations using MM YYYY Only

I am not sure that is correct.  The data needs to be between 1-24 months.  Wouldn't that be >=1 and <=24?

• ###### 3. Re: Date calculations using MM YYYY Only

that would be true if you used datediff    - data add returns a date - so the first date add will return 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.

• ###### 4. Re: Date calculations using MM YYYY Only

So how would I did a between statement then for 1 - 24 months?  You make sense but I cannot get it i to work.

• ###### 5. Re: Date calculations using MM YYYY Only

I would have to see your book to be sure - you could be creating nulls with the calculation and ZN() would help

sum  zn(

(IF [Fiscal Date]  <= dateadd('month',-1,TODAY()) and [Fiscal Date] >= dateadd('month',-24,TODAY())

THEN [FTS Paid] else 0 END))

but without see the data its just guessing

JIm

• ###### 6. Re: Date calculations using MM YYYY Only

Thank You!  That worked perfect!