11 Replies Latest reply on May 8, 2017 5:17 AM by Norbert Maijoor

# How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

Hey Everyone;

The analysis is comparing values of last year vs current year (Year is not fixed, so if you have data from 2014 -2017 than you would compare 2014-2015,2015-2016 & 2016-2017 by filter of course).  The conditions are as follows:

1. PY (Prior Year) uses the Other Amount (Measure)
2. CY (Current Year) uses the USD
3. Comparison would be between prior year Other & Current Year USD as a bar chart (Growth %s using the previous logic)

Appreciate all the help

Kind Regards;

Khalid

• ###### 1. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

Hi Khalid,

I created a separate workbook to explain the principle. In your dataset the years 2015-2016 are only available.

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

1. I created the parameter selector

2. show??: datediff('year',[date],[Selector])>=0 and datediff('year',[date],[Selector])<=1

Regards,

Norbert

• ###### 2. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

Issue with this solution is that I have to constantly add to the parameter, it does not automatically update. Couldn't we create a formula that sets previous year to a measure and current year to another measure.

IF [Date Formula] = "CY" //Date formula determines previous year and another formula to determine current year than create the formula currently shown

THEN {FIXED [Continent],[Country],[Date],[Region],

[Category],[Brand]:SUM([Measure])}

END

Will that work?

• ###### 3. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

Hi Khalid,

May be a "blunt" reply from my side;).

But you could enter x years in the future in your parameter, 2 minutes work, and you are done for the coming X years.

All other approaches are quite more complicated and I am no sure if they are doable.

Or am i missing something?;)

Regards,

Norbert

• ###### 4. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

I have a silly question

Why do we have two data sources & why is there just sales in the data source added?

Also suppose in the analysis I would like to look at 2013-2016 than the parameter would not work (I am developing dashboards/sheets with visuals for others to use). The end user would want to have flexibility in selecting the years.

• ###### 5. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

Hi Khalid,

Upfront, There are no "silly" questions. People are "silly" when they do not state the question;)

I added the second datasource to the workbook because your own dataset has only data for 2015-2016 and I needed more years to explain and validate my approach

If you would like more "flexibility" you can just use filter

Regards,

Norbert

• ###### 6. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

The reason I asked about the other data source is because in my data I have 2 measures. One would be assigned to the current year and the other assigned to the previous year.

So:

2016 would be USD amount

2015 would be Other amount

• ###### 7. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

HI Khalid,

What i see in your dataset. Both values are available for 2015 and 2016

Just as an example I made two calculated fields

2015: if year[Date])=2015 then[Other Amount] END

2016: if year[Date])=2016 then[USD Amount] END

Regards,

Norbert

• ###### 8. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

Yes that would work for both those years, but when we add more data there will be 2017. Is there a formula to call previous year & current year. I used the following formula for previous year (I believe we will face the same problem):

{ MAX(YEAR([Date])) -1}//Previous year

{ MAX(YEAR([Date])) }//Current year

• ###### 9. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

Hi Khalid,

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

CY Other Amount: if DATEDIFF('year',[date],today())=0 then [Other Amount] END

CY USD Amount: if DATEDIFF('year',[date],today())=0 then [USD Amount] END

CY-1 Other Amount: if DATEDIFF('year',[date],today())=1 then [Other Amount] END
CY-1 USD Amount: if DATEDIFF('year',[date],today())=1 then [USD Amount] END

etc

Regards,

Norbert

• ###### 10. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

Thanks!

• ###### 11. Re: How can fix Other Amount to Prior Year/ Previous Year & USD to Current Year?

Hi Khalid.