4 Replies Latest reply on May 24, 2017 11:16 PM by Khalid Baker

# How do I assign USD Amount to Current Year & Other Amount to Previous Year?

Hey Everyone;

I am trying to create a formula where whenever I select any 2 years (2013 & 2014 or 2014 & 2015 or 2015 & 2016), the latest year or current year would be in USD Amount (Measure 1) and prior year as Other Amount (Measure 2). (They are both different measures) (Date (Year) as filter or parameter)

For Example:

Suppose I selected 2015 & 2016 than 2015 is Other Amount & 2016 is USD Amount. Then I decided  to select 2014 & 2015 than 2014 is Other Amount & 2015 is USD Amount.

Hope anyone can assist me with this!

Kind Regards;

Khalid B

• ###### 1. Re: How do I assign USD Amount to Current Year & Other Amount to Previous Year?

Hi Khalid,

To achieve this you have to create a parameter of year and two more calculation along with.

Step 1: Create Year Parameter

Step 2: Create Amount Filed

Step 3: Create Year View

Step 4: Drag Year View in Row or Column Self (Note You will get Null along with two Year values hide Null)

Step 5: Drop your Filed Amount on Text Self

Thanks.

1 of 1 people found this helpful
• ###### 2. Re: How do I assign USD Amount to Current Year & Other Amount to Previous Year?

Alternately, see 10.0 workbook attached.

STEPS:

1. Create this calc: YEAR([Date])  = YEAR( { FIXED  : MAX([Date])})

2. Put this on the color shelf and the size shelf

3. Put Year(Date) on the filter shelf

4. 'Add context' to this date filter

5. Create this calc:  IF [Year = Max Year?] THEN STR([Year]) ELSE 'Other' END

6. Put this calc on the Tooltip shelf and edit tooltip.

Step 4 changes the order of execution so the Year filter will execute before the FIXED LOD. Without the context, MAX(Year) will always be 2016 and will eliminate the max year mark if 2016 is filtered out.

--Shawn

• ###### 3. Re: How do I assign USD Amount to Current Year & Other Amount to Previous Year?

Hey Shawn;

Thanks

Using your method, Will I be able to calculate growth %s using the same logic? (Current Year USD, Prior Year Other)

Kind Regards;

Khalid B

• ###### 4. Re: How do I assign USD Amount to Current Year & Other Amount to Previous Year?

Hey Sandip;

Thanks

Using your method, Will I be able to calculate growth %s using the same logic? (Current Year USD, Prior Year Other)

Essentially, the business users would want an easy to use interactive method, where they would easily analyze the charts without adding years later on. @shawnwallwork The same with your method, does the user have to later add years to the formulas?

Appreciate all the help!

Kind Regards;

Khalid B

1 of 1 people found this helpful