5 Replies Latest reply on Dec 12, 2016 10:12 AM by Andrew Watson

# Display prior year value in current year date in a line graph

Hello,

I am trying to display last year value in a current date graph.  To give you an example, my data (cannot be altered)  is in this format

Date     Value

1/15     10

2/15     10

1/16     20

2/16     20

I currently have a graph that has Date on it axis.  I have a parameter selector that says absolute and prior.  The formulas are as followed

TY

If date <= paramdate and date >= dateadd('week',-8, paramdate) then value else 0 end

LY

Now if I just put them by themselves they put up the correct number, however when I add a filter on a line graph to limit it to paramdate to paramdate - 8 weeks, the LY formulas doesnt work anymore because there is no last year value for the filter  paramdate to paramdate - 8 weeks.  How do I put last year number as this year date.

Thanks

• ###### 1. Re: Display prior year value in current year date in a line graph

If you want to move this year to last year you could create a new date field, to use as your axis and probably in your calculated field:

IF YEAR([OriginalDate]) = YEAR(TODAY()) THEN [OriginalDate] ELSE DATEADD('year',1,[OriginalDate]) END

That formula will align your dates - however I think you will also need another field to identify whether it's this year. You could just use the YEAR([OriginalDate]) to do this, in which case there's no need to create another field.

• ###### 2. Re: Display prior year value in current year date in a line graph

This has started me on the right path.  However I am stuck on 1 last piece now.

Current Smart Date Calculation

CASE [Value Select]

WHEN 1 THEN [DATE]                                         --This Year

WHEN 2 THEN DATEADD('day',364,[DATE])       --Last Year

WHEN 3 THEN DATEADD('day',364,[DATE])       --This Year - Last Year

WHEN 4 THEN DATEADD('day',364,[DATE])       -- (This Year - Last Year)/Last Year

ELSE [True Booked Date] END

The primary problem now is the This Year - Last Year calculation.  Because I now overlay it on the same graph, it does only one and not both.  If I try to throw a CASE statement in there then it will error out for boolean.  Any idea.

• ###### 3. Re: Display prior year value in current year date in a line graph

It's not clear what you're trying to do - the above formula will return the same result for 2, 3 and 4.

However you've asked about 'this year' - 'last year'. To do that you would need to isolate the value for this year and the value for last. For example, assuming your measure is called 'Orders', for this year you could have a formula:

IF YEAR([Date]) = YEAR(TODAY()) THEN [Orders] END

and last year could be:

IF YEAR([Date]) = YEAR(TODAY()) - 1 THEN [Orders] END

Then to find this year minus last year you would use those formulae, i.e. SUM([ThisYear]) - SUM([LastYear])

Hope that points you in the right direction.

• ###### 4. Re: Display prior year value in current year date in a line graph

Andrew,

Thank you for all of your help, I was able to complete the tasks however the senior leadership change a fundamental requirement on me.  They say that the start and end date of the date range must be in a slider format.  This mean the only way to do a slider is to make it a filter.  Since I am making it a filter, my last year value are coming back 0 because of the filter results.  Is there any way to get last year value even with a filtered item?

Thank you,

• ###### 5. Re: Display prior year value in current year date in a line graph

If you're using a filter this will remove last year from the data, as you've found. It is possible to make a 'late' filter using a table calculation, something like LOOKUP(ATTR([Date],0)). However I don't think the table calculation will operate as a slider filter - plus it only affects one worksheet so is less useful on a dashboard with multiple worksheets.

Best solution is to explain to your senior leadership the slider is clunky and won't give them what they want, then revert to how you had it