4 Replies Latest reply on Jan 30, 2018 9:32 AM by Christopher Wen

# Find the max month of all dates in the second most-recent year

Hi,

I have a data set that is a little bit disparate. For example, for half my categories (let's say metrics A and B), I have data for:

April 2016

May 2016

June 2016

July 2016

Jan 2017

Feb 2017

Mar 2017

Dec 2017

For the other half of the categories (let's say metrics C and D), I have data for:

April 2015

May 2015

July 2015

Jan 2016

Aug 2016

In my graph, I want to show the data from the max month of the previous year, as well as data from the max month of the current year. This would be Dec 2017 and July 2016 for the first half of the data; July 2015 and Aug 2016 for the second half of the data. I want this, ideally, to be updated dynamically (no simple manual filtering).

Example Below:

Category July 2015Aug 2016July 2016Dec 2017
Metric A500,000800,000
Metric B46,00042,000
Metric C12,354324
Metric D14,353233
Metric E2424252424

I've written a level of detail calculated filed to get a boolean filter (filter for true) for the max month of the current year, but I cannot figure out how to add onto this filter to also accept the max month of the previous year.

My attempt is below:

[Record Date]={FIXED [Category] : MAX([Record Date])}

OR (datepart('year',[Record Date])={DATEPART('year',MAX([Record Date]))-1}

AND DATEPART('month',[Record Date])={MAX(DATEPART('month',[Record Date]))})

Couple of issues with this expression:

1. I know exactly that the issue is regarding the above is that the MAX(DATEPART('month',[Record Date])) is going to always reflect December, since it is applied to the overall set of [Record Date]. This will cause the July 2015 and July 2016 columns to not show up.
2. The formula has hard-coded the second most recent year to be the most recent year - 1. It is possible I'll have jumps in data over multiple years. Is it possible to make this dynamic?
3. I cannot simply exclude the most recent year, as it is possible that I have older years that also have December. (Btw, side note: I do not know how to just exclude a single year).

Is there a way I can reformat this expression to give the MAX MONTH of the SECOND MOST RECENT YEAR in a dynamic manner?

• ###### 1. Re: Find the max month of all dates in the second most-recent year

I believe I can potentially solve this if I can figure out point #3 by nesting calculated fields.

Find the max date of the set without all items in this year, then apply it to a subsequent calculated field. Anyone know how?

• ###### 2. Re: Find the max month of all dates in the second most-recent year

Hello, I don't now if this will help you but here are my calculations :

PreviousYear : {FIXED [Customer Name]:MAX(YEAR([Order Date]))-1}

PreviousYearLatest : {FIXED [Customer Name]:MAX((IF YEAR([Order Date])=[PreviousYear] THEN [Order Date] END))}

CurrentYearLatest : {FIXED [Customer Name]:MAX((IF YEAR([Order Date])=[PreviousYear]+1 THEN [Order Date] END))}

And if you want exact format as above you can use : DATENAME('month',[PreviousYearLatest ])+" "+DATENAME('year',[PreviousYearLatest ])

1 of 1 people found this helpful
• ###### 3. Re: Find the max month of all dates in the second most-recent year

Sorry, my previous answer is not correct, this is the correct way:

CurrentYear : {FIXED [Customer Name]:MAX(YEAR([Order Date]))}

PreviousYearLatest : {FIXED [Customer Name]:MAX((IF YEAR([Order Date])<[CurrentYear] THEN [Order Date] END))}

CurrentYearLatest : {FIXED [Customer Name]:MAX((IF YEAR([Order Date])=[CurrentYear] THEN [Order Date] END))}

• ###### 4. Re: Find the max month of all dates in the second most-recent year

Thank you! The use of aggregate and non-aggregate functions in level of detail functions is still confusing to me!

For anyone interested, I've combined your PreviousYearLatest and CurrentYearLatest via OR and set it equal to [Order Date] to produce a boolean array that is used to filter.

Thanks again.