I need to calculate Trailing Time period Average for Previous 3 Months (Aug, Sep, Oct) except the Current month with using below calculated field I am not able to achieve it.
Can you help me to achieve this
Trailing Time period 3 Months (Aug, Sep, Oct)
Trailing Time period Calcualted Field : IF [Full Date]>=dateadd('month', -4,today()) and [Full Date]<=today() then [Availability Pct] end
Hi Sai
1. Use this below Calculation
IF DATETRUNC('month', [Full Date]) >=
DATEADD('month',-4,DATETRUNC('month', TODAY()))
AND DATETRUNC('month', [Full Date]) <=
DATEADD('month',-2,DATETRUNC('month',TODAY()))
THEN [Availability Pct] END
or
2. This calculation automatically picks the Previous 3 months from the currently available latest month in the Dataset
IF DATETRUNC('month', [Full Date]) >=
DATEADD('month',-3,DATETRUNC('month',
{MAX(DATETRUNC('month', [Full Date]))}))
AND DATETRUNC('month', [Full Date]) <=
DATEADD('month',-1,DATETRUNC('month',
{MAX(DATETRUNC('month', [Full Date]))}))
THEN [Availability Pct] END
Regards
Munaf
