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

    Christopher Wen

      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?