4 Replies Latest reply on Jun 7, 2018 9:16 AM by Vincent Baumel

    Need help determining penultimate and antepenultimate dates

    Vincent Baumel

      Hi there-

       

      My data set records a new snapshot each day, but replaces the daily snapshot each time unless its the first of the month. Essentially, my date field values look like this:

       

       

      Snapshot Date
      8/1/2017
      9/1/2017
      10/1/2017
      11/1/2017
      12/1/2017
      1/1/2018
      2/1/2018
      3/1/2018
      4/1/2018
      5/1/2018
      6/1/2018
      6/7/2018

       

      I'm trying to write a calculation that only returns true for the 2nd to last and 3rd to last days - the most recent 2 dates that represent the first of the month. I'm working my way towards a month-over-month delta table, then just hiding the first month so that I end up with a single column of metrics that represent MoM change. I've tried all sorts of variations on DATETRUNC('month',MAX([date]))=DATETRUNC('month',TODAY()-30) but I can't quite get it right. Maybe something with DATEPART('day',[date])=1 might do the trick? If anyone has any ideas let me know!

       

      -Vince